using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

namespace Elte.SqlSpIdx.Indices
{
    /// <summary>
    /// This class stands for Balanced K-dimensional Tree 
    /// Includes tree building, nearest neighbour searching and
    /// value estimating based on nearest neighbours
    /// </summary>
    public class BalancedTreeIndex : Index
    {
        private bool optimizedforfirstrecords = true;
        private static Dictionary<string, BalancedTreeIndex> cache = new Dictionary<string, BalancedTreeIndex>();
        private List<double[]> BoundingBoxes = new List<double[]>();
        private List<double[]> BoundingBoxesReal = new List<double[]>();
        private List<double[]> LeafBoxes = new List<double[]>();
        private bool BoundingBoxesLoaded = false;
        private static double[][] memPoints;
        private static string memTable = "";

        private static int[] leaffrom = new int[1];
        private static int[] leafto = new int[1];
        private int rows;

        protected override void DeleteSchema()
        {
            DropTable("Boundingbox_" + this.Name);
        }

        #region Constructors

        /// <summary>
        /// Constructor with Sqlconnection
        /// </summary>
        /// <param name="cn"></param>
        public BalancedTreeIndex(SqlConnection cn)
            : base(cn)
        {
            type = IndexType.BalancedTree;
        }

        /// <summary>
        /// Constructor with SqlTransaction
        /// </summary>
        /// <param name="tn"></param>
        public BalancedTreeIndex(SqlTransaction tn)
            : base(tn)
        {
            type = IndexType.BalancedTree;
        }

        public BalancedTreeIndex()
            : base()
        {
            type = IndexType.BalancedTree;
        }

        #endregion

        /// <summary>
        ///     no new tables requiered   

        /// </summary>
        protected override void CreateSchema()
        {

        }

        //private static BalancedTreeIndex GetIndex(SqlString name)
        //{
        //    BalancedTreeIndex index;

        //    if (!cache.ContainsKey(name.Value))
        //    {
        //        using (SqlConnection cn = new SqlConnection(Constants.MyConnectionString))
        //        {
        //            cn.Open();

        //            index = new BalancedTreeIndex(cn);
        //            index.Load(name.Value);
        //            index.LoadBoxes();
        //            index.DatabaseConnection = cn;
        //            cn.Close();
        //        }

        //        cache.Add(name.Value, index);
        //    }
        //    else
        //    {
        //        index = cache[name.Value];
        //    }

        //    return index;
        //}

        public string CheckDataColumn(string datacolumns)
        {
            string columns = datacolumns;
            if (columns == null)
            {
                columns = "dbo.vector_" + this.Dimensions.ToString().Trim() + "(" + this.DataColumnNames + ")";

            }
            return columns;
        }
        public string CheckIdColumn(string pkcolumn)
        {

            string originalPk = null;
            string originalClusteredIndexName = null;
            string originalClustered = null;
            string sql = Elte.SqlSpIdx.Indices.IndexScripts.GetPKcolumn;
            sql = sql.Replace("$Datatable", Table);
            using (SqlCommand cmd = new SqlCommand(sql, DatabaseConnection, DatabaseTransaction))
            {
                using (SqlDataReader r = cmd.ExecuteReader())
                {
                    if (r.Read())
                    {
                        originalPk = r[0].ToString();
                    }
                }
            }



            if (originalPk == null)
            {
                sql = Elte.SqlSpIdx.Indices.IndexScripts.GetClusteredIndex;
                sql = sql.Replace("$Datatable", Table);
                using (SqlCommand cmd = new SqlCommand(sql, DatabaseConnection, DatabaseTransaction))
                {
                    using (SqlDataReader r = cmd.ExecuteReader())
                    {
                        if (r.Read())
                        {
                            originalClustered = r[1].ToString();
                            originalClusteredIndexName = r[0].ToString();
                        }
                    }
                }

                if (originalClustered == null)
                {

                    Core.Log("Primary key column not existed, creating id_PK column");

                    pkcolumn = "id_PK";

                    sql = Elte.SqlSpIdx.Indices.IndexScripts.AddIdentitycolumn;
                    sql = sql.Replace("$Datatable", Table);
                    sql = sql.Replace("$indexColumn", pkcolumn);
                    using (SqlCommand cmd = new SqlCommand(sql, DatabaseConnection, DatabaseTransaction))
                    {
                        using (SqlDataReader r = cmd.ExecuteReader())
                        {
                            if (r.Read())
                            {
                                originalPk = r[0].ToString();
                            }
                        }
                    }

                }
                else
                {
                    Core.Log("!Warning! Primary key column NOT found, but Clustered index named :" + originalClusteredIndexName + " on column : " + originalClustered + " do exists");
                    Core.Log("(Big)Integer primary key column strongly recommended for good performance, trying to use the " + originalClustered);
                    pkcolumn = originalClustered;
                }
            }
            else
            {
                Core.Log("Primary key column found, hope it is (big)int :" + originalPk);
                if (pkcolumn == null) pkcolumn = originalPk;
                if (originalPk != pkcolumn)
                {
                    throw new Exception("Could not create " + pkcolumn + " column, another primary key column: " + originalPk + " exist!");
                }
            }


            return pkcolumn;
        }


        /// <summary>
        /// Builds a BalancedTree on a SQL table and stores it on the SPIDX table.
        /// Builds a table named Boundingbox_(name) to fill the data of the bounding boxes into.
        /// <note>You can manully modify that table later, but take care to have no gap between boxes</note>
        /// </summary>
        /// <param name="name">The name of the tree (index) to be created </param>
        /// <param name="description">Description of the index</param>
        /// <param name="table">The table name to build on (make sure that the given table exists before running this method)</param>
        /// <param name="primaryKeyColumn">One primary key column (make sure that the given column exists and is a primary key)<note>For performance reasons -especially for large table- this column should be integer and NOT NULL</note></param>
        /// <param name="indexColumn">The column for store the rows tree id (actually is the id of the bouding box)<note>This column is added if not exists, otherwise it become overwritten</note></param>
        /// <param name="dataColumn">not used</param>
        /// <param name="dataColumnNames">the name of data columns (to be indexed) comma seperated. Example: <c>'R,G,Z'</c></param>
        /// <param name="options">specific option to use, treedept and size requiered (same). Example: <c>'size=10,treedepth=10'</c></param>
        /// <param name="InitialBoxSQL">Use valid sql statement to declare the starting space for the index.
        /// Format: x0,x1,y0,y1,...
        /// Leave this parameter to empty string to use the data space
        /// for a 3-dimensional 10x10x10 cube Example: <c>'select  0,10,0,10,0,10'</c>
        /// <note>For general purposes specify this parameter empty, use it when need to estimate for an external table</note></param>
        /// <example>exec BuildBalancedTree 'MyBalancedTree','My first try','Mytable','id','mykd','','A,B,C','size=10,treedepth=10',''</example>

        public override void CreateIndex(string name, string description, string table,
            string primaryKeyColumn, string indexColumn, string dataColumn, string dataColumnNames, string options)
        {

            BalancedTreeIndex index = null;
            bool newtransaction = false;
            if (databaseTransaction == null)
            {
                databaseTransaction = databaseConnection.BeginTransaction();
                newtransaction = true;
            }

            try
            {
                index = new BalancedTreeIndex(databaseTransaction);
                index.Name = name;
                index.Description = (description == null) ? "" : description;
                index.Table = table;
                index.PrimaryKeyColumn = index.CheckIdColumn(primaryKeyColumn);
                index.DataColumnNames = dataColumnNames;
                index.DataColumn = index.CheckDataColumn(dataColumn);
                index.Options = options;

                index.IndexColumn = (indexColumn == null) ? name : indexColumn;
                index.GetMinMax();
                index.GetNumberOfRows();
                index.SetTreeDepth();


                //if (index.Whitening)
                //    index.CalculateWhiteningMatrix();


                index.CreateSchema();
                Core.Log("Tables & Indexes created...");

                Core.Log("Generating tables...");

                newtransaction = false;


                index.Save();
                Core.Log("Index saved...");
                databaseTransaction.Commit();

                DatabaseConnection.Close();
                DatabaseConnection.Open();

                Core.Log("Generating bouding boxes...");
                index.GenerateBoxes(""); //InitialBoxSQL.ToString()
                Core.Log("Creating SQL index...");
                DatabaseConnection.Close();
                DatabaseConnection.Open();

                index.CreateSQLIndex();

            }
            catch (Exception ex)
            {
                Core.Log("Error: " + ex.Message + "\r\n");


            }
            finally
            {
                if (newtransaction) databaseTransaction.Commit();
            }
        }

        private void CreateSQLIndex()
        {
            try
            {


                string sql = BalancedTree.BalancedTreeScripts.KD_CreateSQLIndex;


                string dataColumns = "";
                for (int i = 0; i < Dimensions; i++)
                {
                    dataColumns += dataColumnNamesArray[i] + ((i == Dimensions - 1) ? "" : ",");

                }

                sql = sql.Replace("$Datatable", Table);
                sql = sql.Replace("$indexColumn", IndexColumn);
                sql = sql.Replace("$DataColumns", dataColumns);
                
                using (SqlCommand cmd = new SqlCommand(sql, DatabaseConnection))
                {
                    cmd.CommandTimeout = 0;
                    cmd.ExecuteNonQuery();
                }
            }

            catch (Exception ex)
            {

                Core.Log(ex.Message);
            }

        }

        private void SetTreeDepth()
        {
            if (options.ContainsKey("treedepth")) treedepth = int.Parse(options["treedepth"]);
            else
            {
                treedepth = (int)(Math.Log(this.rows, 2) / 2 + 1);
                if (treedepth < 1) treedepth = 1;
                options.Add("treedepth", treedepth.ToString());
            }
        }


        private int treedepth = 0;
        private int TreeDepth
        {
            get
            {
                if (treedepth == 0)
                {
                    if (options.ContainsKey("treedepth")) treedepth = int.Parse(options["treedepth"]);
                }
                return treedepth;
            }
        }

        /// <summary>
        /// Generates the bounding boxes and store them.
        /// BuildBalancedTree method calls it, but can be manually invoked later.
        /// Uses kd_temp1, kd_temp2 tables for temporary purposes
        /// </summary>
        /// <param name="InitialBoxSQL">Use valid sql statement to declare the starting space for the index.
        /// Format: x0,x1,y0,y1,...
        /// Leave this parameter to empty string to use the data space
        /// <example>for a 3-dimensional 10x10x10 cube 'select  0,10,0,10,0,10'</example>
        /// <note>For general purposes specify this parameter empty, use it when need to estimate for an external table</note>
        /// </param>
        protected void GenerateBoxes(string InitialBoxSQL)
        {
            System.Diagnostics.Stopwatch s = new System.Diagnostics.Stopwatch();
            s.Start();

            // 2 temp tables
            CreateTable(BalancedTree.BalancedTreeScripts.KD_CreateTableTemp, "kd_temp1", "", true);
            CreateTable(BalancedTree.BalancedTreeScripts.KD_CreateTableTemp, "kd_temp2", "", true);
            string additionalcolumns = "";
            for (int i = 0; i < this.Dimensions; i++)
            {
                additionalcolumns += ",min_" + this.dataColumnNamesArray[i] + " float not null";
                additionalcolumns += ",max_" + this.dataColumnNamesArray[i] + " float not null";
                //Index for the data table
                //                CreateIndex( this.Table, this.PrimaryKeyColumn, this.dataColumnNamesArray[i]);
            }
            for (int i = 0; i < this.Dimensions; i++)
            {
                additionalcolumns += ",minB_" + this.dataColumnNamesArray[i] + " float not null";
                additionalcolumns += ",maxB_" + this.dataColumnNamesArray[i] + " float not null";
            }
            //Create the bounding boxes table
            CreateTable(BalancedTree.BalancedTreeScripts.KD_CreateTableBoundingBox, "Boundingbox_" + this.Name, additionalcolumns, true);

            ////for each level of the tree
            string sql;
            for (int i = 0; i < this.TreeDepth; i++)
            {

                if (i == 0)
                    sql = Elte.SqlSpIdx.Indices.BalancedTree.BalancedTreeScripts.kd_createfirsttemp;
                else
                    sql = Elte.SqlSpIdx.Indices.BalancedTree.BalancedTreeScripts.KD_CreateKdTemp;
                if (i % 2 == 0)
                {
                    sql = sql.Replace("$temptable1", Table + Constants.SPIDX + Name + "_kd_temp1");
                    sql = sql.Replace("$temptable2", Table + Constants.SPIDX + Name + "_kd_temp2");
                }
                else
                {
                    sql = sql.Replace("$temptable1", Table + Constants.SPIDX + Name + "_kd_temp2");
                    sql = sql.Replace("$temptable2", Table + Constants.SPIDX + Name + "_kd_temp1");
                }
                sql = sql.Replace("$Datatable", Table);
                sql = sql.Replace("$IndexId", PrimaryKeyColumn);
                sql = sql.Replace("[$DataColumn]", this.DataColumnNameForSelect(i % dataColumnNamesArray.Length));
                sql = sql.Replace("$Number", ((int)(rows / Math.Pow(2, i + 1))).ToString());
                DatabaseConnection.Close();
                DatabaseConnection.Open();
                using (SqlCommand cmd = new SqlCommand(sql, DatabaseConnection))
                {
                    cmd.CommandTimeout = 0;
                    cmd.ExecuteNonQuery();
                }
                Core.Log("level " + i.ToString() + "  : " + s.ElapsedMilliseconds.ToString() + " ms elapsed (total)");
            }
            DatabaseConnection.Close();
            DatabaseConnection.Open();
            if (this.options.ContainsKey("SeparatedIndexColumn") && this.options["SeparatedIndexColumn"].ToLower() == "yes")
            {
                //CreateIndex("", "", "", true);
                //using (SqlCommand cmd = new SqlCommand(sql, DatabaseConnection))
                //{
                //    try
                //    {
                //        cmd.ExecuteNonQuery();

                //    }
                //}
            }
            else
            {

                //add the new column for the target table
                sql = Elte.SqlSpIdx.Indices.IndexScripts.Addcolumn;
                sql = sql.Replace("$Datatable", Table);
                sql = sql.Replace("$indexColumn", IndexColumn);
                using (SqlCommand cmd = new SqlCommand(sql, DatabaseConnection))
                {
                    cmd.ExecuteNonQuery();
                }

                //update the index column
                sql = Elte.SqlSpIdx.Indices.BalancedTree.BalancedTreeScripts.kd_tableupdate;
                sql = sql.Replace("$Datatable", Table);
                sql = sql.Replace("$indexColumn", IndexColumn);
                sql = sql.Replace("$IndexId", PrimaryKeyColumn);
                sql = sql.Replace("$temptable", Table + Constants.SPIDX + Name + "_" + ((TreeDepth % 2 == 0) ? "kd_temp2" : "kd_temp1"));
                sql = sql.Replace("$offset", Math.Pow(2, TreeDepth).ToString());
                using (SqlCommand cmd = new SqlCommand(sql, DatabaseConnection))
                {
                    cmd.CommandTimeout = 0;
                    cmd.ExecuteNonQuery();
                }
            }
            DatabaseConnection.Close();
            DatabaseConnection.Open();
            //Fill the bounding boxes
            Core.Log("Populating bounding box table... ");

            string minmax = "";
            sql = Elte.SqlSpIdx.Indices.BalancedTree.BalancedTreeScripts.KD_BoundingBoxInsert;
            sql = sql.Replace("$BoxTable", Table + Constants.SPIDX + Name + "_" + "Boundingbox_" + this.Name);
            sql = sql.Replace("$indexColumn", IndexColumn);
            sql = sql.Replace("$Datatable", Table);
            sql = sql.Replace("$stage", this.TreeDepth.ToString());
            for (int i = 0; i < this.Dimensions; i++)
            {
                if (i != 0) minmax += ",";
                minmax += "min(" + this.DataColumnNameForSelect(i) + "),max(" + this.DataColumnNameForSelect(i) + ")";
            }
            for (int i = 0; i < this.Dimensions; i++) { minmax += ",0,0"; }
            sql = sql.Replace("$MinMax", minmax);
            using (SqlCommand cmd = new SqlCommand(sql, DatabaseConnection))
            {
                cmd.CommandTimeout = 0;
                cmd.ExecuteNonQuery();
            }

            //For each stage insert the bounding boxes
            for (int stage = this.TreeDepth; stage > 0; stage--)
            {
                sql = Elte.SqlSpIdx.Indices.BalancedTree.BalancedTreeScripts.kd_boundingboxfill;
                sql = sql.Replace("$BoxTable", Table + Constants.SPIDX + Name + "_" + "Boundingbox_" + this.Name);
                sql = sql.Replace("$Stage", stage.ToString());
                minmax = "";
                for (int i = 0; i < this.Dimensions; i++)
                {
                    minmax += ",min(min_" + this.dataColumnNamesArray[i] + "),max(max_" + this.dataColumnNamesArray[i] + ")";
                }
                for (int i = 0; i < this.Dimensions; i++) { minmax += ",0,0"; }

                sql = sql.Replace("$MinMax", minmax);
                DatabaseConnection.Close();
                DatabaseConnection.Open();
                using (SqlCommand cmd = new SqlCommand(sql, DatabaseConnection))
                {
                    cmd.CommandTimeout = 0;
                    cmd.ExecuteNonQuery();
                }
            }

            //if no initial box specified use the data table
            double[] startbox = new double[this.Dimensions * 2];
            if (InitialBoxSQL != "")
            {
                sql = InitialBoxSQL;
                using (SqlCommand cmd = new SqlCommand(sql, DatabaseConnection))
                {
                    using (SqlDataReader r = cmd.ExecuteReader())
                    {
                        if (r.Read())
                        {

                            for (int i = 0; i < this.Dimensions * 2; i++)
                            {
                                startbox[i] = (double)r[i];
                            }
                        }
                    }
                }


            }

            //update the bounding box table, for the real boxes
            string actualcolname = "";
            for (int stage = 0; stage <= this.TreeDepth; stage++)
            {
                sql = Elte.SqlSpIdx.Indices.BalancedTree.BalancedTreeScripts.kd_boundingboxupdate;
                sql = sql.Replace("$BoxTable", Table + Constants.SPIDX + Name + "_" + "Boundingbox_" + this.Name);
                sql = sql.Replace("$stage", stage.ToString());
                minmax = "";
                if (stage == 0)
                {
                    for (int i = 0; i < this.Dimensions; i++)
                    {
                        if (i != 0) minmax += ",";
                        actualcolname = this.dataColumnNamesArray[i];
                        if (InitialBoxSQL != "")
                        {
                            minmax += "minB_" + actualcolname + "=" + startbox[i * 2].ToString() + ",maxB_" + actualcolname + "=" + startbox[i * 2 + 1].ToString();
                        }
                        else
                        {
                            minmax += "minB_" + actualcolname + "=B.min_" + actualcolname + ",maxB_" + actualcolname + "=B.max_" + actualcolname;
                        }
                    }

                }
                else
                {
                    for (int i = 0; i < this.Dimensions; i++)
                    {
                        if (i != 0) minmax += ",";
                        actualcolname = this.dataColumnNamesArray[i];
                        if ((stage - 1) % this.Dimensions == i)
                        {
                            minmax += "minB_" + actualcolname + "=case when A.leaf%2=0 then B.minB_" + actualcolname + " else (C.max_" + actualcolname + "+A.min_" + actualcolname + ")/2 end,";
                            minmax += "maxB_" + actualcolname + "=case when A.leaf%2=1 then B.maxB_" + actualcolname + " else (C.min_" + actualcolname + "+A.max_" + actualcolname + ")/2 end";
                        }
                        else
                        {
                            minmax += "minB_" + actualcolname + "=B.minB_" + actualcolname + ",maxB_" + actualcolname + "=B.maxB_" + actualcolname;
                        }

                    }
                }
                sql = sql.Replace("$MinMax", minmax);
                if (stage == 0) sql = sql.Replace("floor(A.leaf/2)=B.leaf", "1=1");

                if (stage == 0) sql = sql.Replace("A.stage=B.stage+1", "A.stage=B.stage");
                DatabaseConnection.Close();
                DatabaseConnection.Open();
                using (SqlCommand cmd = new SqlCommand(sql, DatabaseConnection))
                {
                    cmd.CommandTimeout = 0;
                    cmd.ExecuteNonQuery();
                }


            }
            if (!this.BinaryDataColumn)
            {
                ////Create the index for fast data access through the kd-tree             
                //string columnsforindex = "";
                //for (int i = 0; i < this.Dimensions; i++)
                //{
                //    if (i != 0) columnsforindex += ",";
                //    columnsforindex += this.dataColumnNamesArray[i];
                //}
            }

            //Drop the 2 temp table
            DropTable("kd_temp1");
            DropTable("kd_temp2");

        }

        /// <summary>
        /// Load boxes from the data table into the memory
        /// </summary>
        private void LoadBoxes()
        {
            if (BoundingBoxesLoaded) return;
            //Core.Log("Bounding boxes loaded...");
            BoundingBoxes = new List<double[]>();
            LeafBoxes = new List<double[]>();
            BoundingBoxes.Add(new double[0]);
            BoundingBoxesReal.Add(new double[0]);
            double[] Box = new double[this.Dimensions * 2];
            string sql = Elte.SqlSpIdx.Indices.BalancedTree.BalancedTreeScripts.kd_loadboundingbox;
            sql = sql.Replace("$BoxTable", Table + Constants.SPIDX + Name + "_" + "Boundingbox_" + this.Name);
            string columns = "";
            for (int i = 0; i < this.Dimensions; i++)
            {
                if (i != 0) columns += ",";
                columns += "minB_" + this.dataColumnNamesArray[i];
                columns += ",maxB_" + this.dataColumnNamesArray[i];
            }
            columns += ",";
            for (int i = 0; i < this.Dimensions; i++)
            {
                if (i != 0) columns += ",";
                columns += "min_" + this.dataColumnNamesArray[i];
                columns += ",max_" + this.dataColumnNamesArray[i];
            }
            columns += ",stage";
            sql = sql.Replace("$columns", columns);


            using (SqlCommand cmd = new SqlCommand(sql, DatabaseConnection))
            {
                using (SqlDataReader r = cmd.ExecuteReader())
                {
                    while (r.Read())
                    {
                        Box = new double[this.Dimensions * 2];
                        for (int i = 0; i < this.Dimensions; i++)
                        {
                            Box[i * 2] = (double)r[i * 2];
                            Box[i * 2 + 1] = (double)r[i * 2 + 1];
                        }
                        BoundingBoxes.Add(Box);

                        Box = new double[this.Dimensions * 2];
                        for (int i = 0; i < this.Dimensions; i++)
                        {
                            Box[i * 2] = (double)r[i * 2 + Dimensions * 2];
                            Box[i * 2 + 1] = (double)r[i * 2 + 1 + Dimensions * 2];
                        }
                        BoundingBoxesReal.Add(Box);

                        if ((int)r[Dimensions * 2 * 2] == this.TreeDepth)
                        {

                            LeafBoxes.Add(Box);
                        }
                    }
                }
            }
            BoundingBoxesLoaded = true;
        }


        /// <summary>
        /// Returns the edge points (and possible point for the bounding point list) of a given box
        /// internal use only
        /// </summary>
        /// <param name="actualBox">id number of the box</param>
        /// <param name="startpoint">start point (for the the bounding point list calculation)</param>
        /// <returns>List of points</returns>
        protected List<double[]> GetBoxPoints(int actualBox, double[] startpoint)
        {
            int dim = this.Dimensions;
            int numberofpoints = (int)Math.Pow(3, this.Dimensions);
            int mod = 3;
            double[] box = BoundingBoxes[actualBox];
            List<double[]> pointstoreturn = new List<double[]>();
            double[] actualpoint;
            bool validpoint = true;
            for (int i = 0; i < numberofpoints; i++)
            {
                actualpoint = new double[dim + 1];
                validpoint = true;
                mod = 1;
                for (int k = 0; k < dim; k++)
                {
                    if ((int)(i / mod) % 3 == 0) { actualpoint[k] = box[k * 2]; }
                    if ((int)(i / mod) % 3 == 1)
                    {
                        if (startpoint[k] > box[k * 2] && startpoint[k] < box[k * 2 + 1])
                        { actualpoint[k] = startpoint[k]; }
                        else { validpoint = false; break; }
                    }
                    if ((int)(i / mod) % 3 == 2) { actualpoint[k] = box[k * 2 + 1]; }
                    mod *= 3;
                }
                if (validpoint)
                {
                    actualpoint[dim] = GetDistance(startpoint, actualpoint, dim);
                    pointstoreturn.Add(actualpoint);
                }
            }
            return pointstoreturn;
        }

        /// <summary>
        /// Calculates the square of the distance between 2 points
        /// </summary>
        /// <param name="P1">1st point</param>
        /// <param name="P2">2nd point</param>
        /// <param name="Dimension">number of dimensions to use</param>
        /// <note>method uses only the specified first D dimension</note>
        /// <returns>distance</returns>
        public static double GetDistance(double[] P1, double[] P2, int Dimension)
        {
            double distance = 0;
            for (int i = 0; i < Dimension; i++)
            {
                distance += (P1[i] - P2[i]) * (P1[i] - P2[i]);
            }
            //SQRT required for the real distance, but it is faster without it
            return (distance);
        }



        /// <summary>
        /// Determenis wheter two double arrays are equals in values
        /// </summary>
        /// <param name="f1">1st double array</param>
        /// <param name="f2">2nd double array</param>
        /// <param name="dim">first D dimension to investiage</param>
        /// <returns>true if equals, else false</returns>
        public static bool EqualdoubleArray(double[] f1, double[] f2, int dim)
        {
            for (int i = 0; i < dim; i += 1)
            {
                if (f1[i] != f2[i])
                {
                    return false;
                }
            }
            return true;
        }






        /// <summary>
        /// Gets the number of rows in the data table
        /// </summary>
        private void GetNumberOfRows()
        {
            string sql = Elte.SqlSpIdx.Indices.BalancedTree.BalancedTreeScripts.KD_GetNumberOfRows;
            sql = sql.Replace("$Tablename", this.Table);

            using (SqlCommand cmd = new SqlCommand(sql, DatabaseConnection, DatabaseTransaction))
            {
                this.rows = (int)cmd.ExecuteScalar();
            }

        }




        #region "SQLCRL Index functions"


        //public override IEnumerable SpherePoints(double[] center, double R)
        //{
        //    string columns = "";
        //    this.LoadBoxes();

        //    int actualBox = 1;
        //    for (int actualstage = 0; actualstage < this.TreeDepth; actualstage++)
        //    {
        //        actualBox *= 2;
        //        for (int k = 0; k < Dimensions; k++)
        //        {
        //            //min or max
        //            if ((BoundingBoxes[actualBox][k * 2] > center[k]) || (BoundingBoxes[actualBox][k * 2 + 1] <= center[k]))
        //            {
        //                actualBox++;
        //                break;
        //            }
        //        }
        //    }
        //    return actualBox;


        //    //add the distance (square) to the columns
        //    string distancecolumn = "";
        //    for (int i = 0; i < this.Dimensions; i++)
        //    {
        //        if (i != 0) distancecolumn += "+";
        //        distancecolumn += ("((" + this.DataColumnNameForSelect(i) + "-(" + center[i].ToString(System.Globalization.CultureInfo.InvariantCulture.NumberFormat) + "))*(" + this.DataColumnNameForSelect(i) + "-(" + center[i].ToString(System.Globalization.CultureInfo.InvariantCulture.NumberFormat) + ")))");
        //    }
        //    //if (this.BinaryDataColumn)
        //    //{
        //    //    distancecolumn = " dbo.VectorDistance(" + this.DataColumn + "," + this.DataColumn + ")";
        //    //}
        //    columns += distancecolumn;
        //    columns += ",";
        //    columns += this.PrimaryKeyColumn;
        //    if (optimizedforfirstrecords)
        //    {
        //        return PointQuery(center, 0, R, columns, "");
        //    }
        //    throw new Exception("The method or operation is not implemented.");
        //}

        public override IEnumerable NearestNeighborsIndex(double[] center, int count)
        {
            string columns = "";



            //add the distance (square) to the columns
            string distancecolumn = "";
            for (int i = 0; i < this.Dimensions; i++)
            {
                if (i != 0) distancecolumn += "+";
                distancecolumn += ("((" + this.DataColumnNameForSelect(i) + "-(" + center[i].ToString(System.Globalization.CultureInfo.InvariantCulture.NumberFormat) + "))*(" + this.DataColumnNameForSelect(i) + "-(" + center[i].ToString(System.Globalization.CultureInfo.InvariantCulture.NumberFormat) + ")))");
            }
            //if (this.BinaryDataColumn)
            //{
            //    distancecolumn = " dbo.VectorDistance(" + this.DataColumn + "," + this.DataColumn + ")";
            //}
            columns += distancecolumn;
            columns += ",";
            columns += this.PrimaryKeyColumn;



            return PointQuery(center, count, 0, columns, "NN");
        }

        public IEnumerable NearestNeighborsIndexYieldReturn(double[] center, int count)
        {
            string columns = "";



            //add the distance (square) to the columns
            string distancecolumn = "";
            for (int i = 0; i < this.Dimensions; i++)
            {
                if (i != 0) distancecolumn += "+";
                distancecolumn += ("((" + this.DataColumnNameForSelect(i) + "-(" + center[i].ToString(System.Globalization.CultureInfo.InvariantCulture.NumberFormat) + "))*(" + this.DataColumnNameForSelect(i) + "-(" + center[i].ToString(System.Globalization.CultureInfo.InvariantCulture.NumberFormat) + ")))");
            }
            //if (this.BinaryDataColumn)
            //{
            //    distancecolumn = " dbo.VectorDistance(" + this.DataColumn + "," + this.DataColumn + ")";
            //}
            columns += distancecolumn;
            columns += ",";
            columns += this.PrimaryKeyColumn;



            return PointQuery(center, count, 0, columns, "NN");
        }
        private IEnumerable<int> Cellids(string spatialwhere)
        {
            if (spatialwhere.StartsWith("distance")) return CellidsSphere(spatialwhere);
            if (spatialwhere.StartsWith("nn")) return CellidsNN(spatialwhere);
            return null;
        }
        private IEnumerable<int> CellidsNN(string spatialwhere)
        {
            //NN(cx=1,cy=2,cz=3,N=4)
            this.LoadBoxes();
            double[] center = new double[this.Dimensions];
            int N;
            spatialwhere = spatialwhere.ToLower();
            if (spatialwhere.StartsWith(@"nn("))
            {
                spatialwhere = spatialwhere.Remove(0, 3);
                for (int i = 0; i < this.Dimensions + 1; i++)
                {
                    spatialwhere = spatialwhere.Remove(0, spatialwhere.IndexOf("=") + 1);
                    if (i < this.Dimensions) center[i] = double.Parse(spatialwhere.Substring(0, spatialwhere.Contains(",") ? spatialwhere.IndexOf(",") : spatialwhere.IndexOf(@")")), System.Globalization.CultureInfo.InvariantCulture.NumberFormat);
                    else N = int.Parse(spatialwhere.Substring(0, spatialwhere.Contains(",") ? spatialwhere.IndexOf(",") : spatialwhere.IndexOf(@")")), System.Globalization.CultureInfo.InvariantCulture.NumberFormat);
                    spatialwhere = spatialwhere.Remove(0, (spatialwhere.Contains(",") ? spatialwhere.IndexOf(",") : spatialwhere.IndexOf(@")")) + 1);
                }
            }
            //foreach (int i in CellidsSphereRecursive(center, N, 1)) yield return i;
            return null;
        }

        private IEnumerable<int> CellidsSphereRecursive(double[] center, double radiussqr, int cellid)
        {
            //nearest distance
            double distancemin = 0;
            //farest distance
            double distancemax = 0;
            double distanceup, distancedown;
            for (int i = 0; i < this.Dimensions; i++)
            {
                distanceup = BoundingBoxes[cellid][i * 2] - center[i];
                distancedown = BoundingBoxes[cellid][i * 2 + 1] - center[i];
                if (distanceup < 0)
                {
                    distanceup = -distanceup;
                    distancedown = -distancedown;

                }
                if (distancedown <= 0)
                {
                    if (distanceup > -distancedown)
                    {
                        distancemax += distanceup * distanceup;
                    }
                    else
                    {
                        distancemax += distancedown * distancedown;
                    }
                }
                else
                    if (distancedown < distanceup)
                    {
                        distancemin += distancedown * distancedown;
                        distancemax += distanceup * distanceup;
                    }
                    else
                    {
                        distancemin += distanceup * distanceup;
                        distancemax += distancedown * distancedown;
                    }

            }
            if (distancemin <= radiussqr)
            {
                if (distancemax <= radiussqr) yield return cellid;
                else
                {
                    if (cellid >= Math.Pow(2, this.TreeDepth)) yield return cellid;
                    else
                    {
                        foreach (int i in CellidsSphereRecursive(center, radiussqr, cellid * 2)) yield return i;
                        foreach (int i in CellidsSphereRecursive(center, radiussqr, cellid * 2 + 1)) yield return i;
                    }
                }
            }
        }
        private IEnumerable<int> CellidsSphere(string spatialwhere)
        {
            //distance(cx=1,cy=2,cz=3)<5;
            this.LoadBoxes();
            double[] center = new double[this.Dimensions];
            double radius = 0;
            spatialwhere = spatialwhere.ToLower();
            if (spatialwhere.StartsWith(@"distance("))
            {
                spatialwhere = spatialwhere.Remove(0, 9);
                for (int i = 0; i < this.Dimensions; i++)
                {
                    spatialwhere = spatialwhere.Remove(0, spatialwhere.IndexOf("=") + 1);
                    center[i] = double.Parse(spatialwhere.Substring(0, spatialwhere.Contains(",") ? spatialwhere.IndexOf(",") : spatialwhere.IndexOf(@")")), System.Globalization.CultureInfo.InvariantCulture.NumberFormat);
                    spatialwhere = spatialwhere.Remove(0, (spatialwhere.Contains(",") ? spatialwhere.IndexOf(",") : spatialwhere.IndexOf(@")")) + 1);
                }
                spatialwhere = spatialwhere.Trim();
                spatialwhere = spatialwhere.Remove(0, 1);
                radius = double.Parse(spatialwhere, System.Globalization.CultureInfo.InvariantCulture.NumberFormat);
            }
            //int laststagemin = (int)Math.Pow(2, TreeDepth);
            //int laststagemax = (int)Math.Pow(2, TreeDepth + 1) - 1;
            //int leveltoadd;
            //int currentcell;
            foreach (int i in CellidsSphereRecursive(center, radius * radius, 1))
            {
                //currentcell = i;
                //leveltoadd = 0;
                //while (!(laststagemin <= currentcell && currentcell <= laststagemax))
                //{
                //    leveltoadd++;
                //    currentcell *= 2;
                //}
                //for (int shift = 0; shift < Math.Pow(2, leveltoadd); shift++)
                //{
                //    yield return currentcell + shift;
                //}
                yield return i;
            }

        }
        //private int laststagemin = 0;
        //private int laststagemax = 0;
        //private IEnumerable<int> CellidsLastStage(int id)
        //{
        //    currentcell = i;
        //    leveltoadd = 0;
        //    while (!(laststagemin <= currentcell && currentcell <= laststagemax))
        //    {
        //        leveltoadd++;
        //        currentcell *= 2;
        //    }
        //    for (int shift = 0; shift < Math.Pow(2, leveltoadd); shift++)
        //    {
        //        yield return currentcell + shift;
        //    }
        //}
        private IEnumerable CellidsIteratingFromOnePoint(string spatialwhere)
        {
            //Leaves.Add(ActualCell);
            ////add the bounding points of it
            //bool contains = false;
            //foreach (double[] actualpoint in this.GetBoxPoints(ActualCell, center))
            //{
            //    //Previous point?
            //    foreach (double[] boundpoint in BoundPointsCompleted)
            //    {
            //        contains = true;
            //        for (int i = 0; i < dim; i += 1)
            //        {
            //            if (boundpoint[i] != actualpoint[i])
            //            {
            //                contains = false;
            //                break;
            //            }
            //        }
            //        if (contains) break;
            //    }
            //    //Already in actual list?
            //    if (!contains)
            //    {
            //        foreach (double[] boundpoint in Bounds)
            //        {
            //            contains = true;
            //            for (int i = 0; i < dim; i += 1)
            //            {
            //                if (boundpoint[i] != actualpoint[i])
            //                {
            //                    contains = false;
            //                    break;
            //                }
            //            }
            //            if (contains) break;
            //        }
            //    }
            //    if (!contains)
            //    {
            //        Bounds.Add(actualpoint);
            //    }
            //}
            ////int[]  Clls = this.GetLeavesInvolved(dim, Bounds,center);
            //Boolean foundnewBox = false;
            //while (!foundnewBox && Bounds.Count > 0)
            //{
            //    //Search for the nearest bounding box point
            //    //double nearestboundingdistance = 0;

            //    //The index of this point
            //    //int nearestboundingindex = 0;
            //    nearestboundingdistance = double.MaxValue;
            //    nearestboundingindex = 0;
            //    for (int i = 0; i < Bounds.Count; i++)
            //    {
            //        if (Bounds[i][dim] < nearestboundingdistance)
            //        {
            //            nearestboundingdistance = Bounds[i][dim];
            //            nearestboundingindex = i;
            //        }

            //    }
            //    double[] searchpoint = new double[dim];
            //    for (int i = 0; i < dim; i++)
            //    {
            //        searchpoint[i] = Bounds[nearestboundingindex][i];
            //        if (Bounds[nearestboundingindex][i] > center[i]) searchpoint[i] += 0.1F;
            //        if (Bounds[nearestboundingindex][i] < center[i]) searchpoint[i] -= 0.1F;

            //    }

            //    ActualCell = this.LookupIndex(searchpoint);
            //    BoundPointsCompleted.Add(Bounds[nearestboundingindex]);
            //    Bounds.RemoveAt(nearestboundingindex);
            //    //if already contains it continue with the next point
            //    if (!Leaves.Contains(ActualCell)) foundnewBox = true;
            //}






            return null;
        }

        public static System.Diagnostics.Stopwatch s = new System.Diagnostics.Stopwatch();
        public static string output = "";

        private IEnumerable PointQuery(double[] center, int count, double radius, string columns, string where)
        {

            List<KeyValuePair<long, double>> result = new List<KeyValuePair<long, double>>();
            //how many row returned?
            int returnedcount = 0;

            int dim = this.Dimensions;
            string distancecolumn = "";
            for (int i = 0; i < dim; i++)
            {
                if (i != 0) distancecolumn += "+";
                distancecolumn += ("((" + this.DataColumnNameForSelect(i) + "-(" + center[i].ToString(System.Globalization.CultureInfo.InvariantCulture.NumberFormat) + "))*(" + this.DataColumnNameForSelect(i) + "-(" + center[i].ToString(System.Globalization.CultureInfo.InvariantCulture.NumberFormat) + ")))");
            }
            //the Kd tree cells (ids) which have been queried already from the sql server
            System.Collections.ArrayList Leaves = new System.Collections.ArrayList();

            //The actual nearest vectors, (ids)
            long[] nearestpoints = new long[count];
            //The actual nearest vectors, (distance)
            double[] nearestpointsdistance = new double[count];
            for (int k = 0; k < count; k++)
            {
                nearestpoints[k] = 0;
                nearestpointsdistance[k] = double.MaxValue;
            }

            //The actual farest point distance
            double farestpointdistance = double.MaxValue;

            //The index of this point
            int farestpointindex = 0;

            //The actual nearest bounding box point
            double nearestboundingdistance = 0;

            //The index of this point
            int nearestboundingindex = 0;

            //The actual Bounding points
            //empty at start
            //key is the distance from the start point
            List<double[]> Bounds = new List<double[]>();

            int numberofRows = 0;



            //The points which completed, no need to register them again..
            List<double[]> BoundPointsCompleted = new List<double[]>();
            BoundPointsCompleted.Add(center);

            //First determine in which cells the point belongs to
            int ActualCell = this.LookupIndex(center);


            long queriedpoint = 0;
            double queriedpointdistance;

            Boolean readsucc = false;
            if (radius == 0)
            {

                farestpointdistance = nearestpointsdistance[count - 1];
            }
            else
            {
                farestpointdistance = radius * radius;
            }

            while (farestpointdistance > nearestboundingdistance)
            {

                farestpointindex = 0;

                //cellread++;
                //Add new Cell
                //Read points from DB
                string sql = Elte.SqlSpIdx.Indices.BalancedTree.BalancedTreeScripts.KD_QueryBox;

                sql = sql.Replace("$columns", ((count == 0) ? "" : (" top " + count.ToString())) + " " + columns);
                sql = sql.Replace("$Table", this.Table);
                sql = sql.Replace("$Indexname", this.IndexColumn);
                sql = sql.Replace("$cellnumber", ActualCell.ToString());
                sql = sql.Replace("[$where]", "");

                if (where == "NN")
                {
                    if (farestpointdistance != double.MaxValue)
                    {
                        sql += " and " + distancecolumn + " < " + farestpointdistance.ToString(System.Globalization.CultureInfo.InvariantCulture.NumberFormat);
                    }
                    sql += " order by " + distancecolumn;
                }
                else
                {
                    sql += where;
                }


                using (SqlCommand cmd = new SqlCommand(sql, this.databaseConnection))
                {
                    using (SqlDataReader r = cmd.ExecuteReader())
                    {
                        readsucc = r.Read();
                        while (readsucc)
                        {
                            numberofRows++;
                            queriedpointdistance = Common.UnboxDouble(r[0]);
                            if (queriedpointdistance < farestpointdistance)
                            {
                                if (count != 0)
                                {
                                    queriedpoint = Common.UnboxLong(r[1]);
                                    while (farestpointindex < count && nearestpointsdistance[farestpointindex] <= queriedpointdistance)
                                    {
                                        farestpointindex++;
                                    }
                                    if (farestpointindex >= count)
                                    {
                                        farestpointdistance = nearestpointsdistance[count - 1];
                                        break;
                                    }
                                    for (int i = count - 1; i > farestpointindex; i--)
                                    {
                                        nearestpoints[i] = nearestpoints[i - 1];
                                        nearestpointsdistance[i] = nearestpointsdistance[i - 1];
                                    }
                                    nearestpoints[farestpointindex] = queriedpoint;
                                    nearestpointsdistance[farestpointindex] = queriedpointdistance;
                                    farestpointindex++;
                                    farestpointdistance = nearestpointsdistance[count - 1];
                                    if (farestpointindex >= count)
                                    {
                                        break;
                                    }
                                }
                                else
                                {
                                    if (Microsoft.SqlServer.Server.SqlContext.IsAvailable)
                                    {

                                        // yield return r;
                                        result.Add(new KeyValuePair<long, double>(queriedpoint, queriedpointdistance));
                                    }
                                    else
                                    {
                                        object[] values = new object[r.FieldCount];
                                        r.GetValues(values);
                                        for (int k = 0; k < r.FieldCount; k++)
                                        {
                                            if (values[k] != null)
                                            {
                                                Console.Write(values[k].ToString());
                                                Console.Write(",");
                                            }
                                        }

                                    }


                                }
                            }
                            readsucc = r.Read();
                        }

                    }

                }

                Leaves.Add(ActualCell);
                //add the bounding points of it
                bool contains = false;
                foreach (double[] actualpoint in this.GetBoxPoints(ActualCell, center))
                {
                    //Previous point?
                    foreach (double[] boundpoint in BoundPointsCompleted)
                    {
                        contains = true;
                        for (int i = 0; i < dim; i += 1)
                        {
                            if (boundpoint[i] != actualpoint[i])
                            {
                                contains = false;
                                break;
                            }
                        }
                        if (contains) break;
                    }
                    //Already in actual list?
                    if (!contains)
                    {
                        foreach (double[] boundpoint in Bounds)
                        {
                            contains = true;
                            for (int i = 0; i < dim; i += 1)
                            {
                                if (boundpoint[i] != actualpoint[i])
                                {
                                    contains = false;
                                    break;
                                }
                            }
                            if (contains) break;
                        }
                    }
                    if (!contains)
                    {
                        Bounds.Add(actualpoint);
                    }
                }
                //int[]  Clls = this.GetLeavesInvolved(dim, Bounds,center);
                Boolean foundnewBox = false;
                while (!foundnewBox && Bounds.Count > 0)
                {
                    //Search for the nearest bounding box point
                    //double nearestboundingdistance = 0;

                    //The index of this point
                    //int nearestboundingindex = 0;
                    nearestboundingdistance = double.MaxValue;
                    nearestboundingindex = 0;
                    for (int i = 0; i < Bounds.Count; i++)
                    {
                        if (Bounds[i][dim] < nearestboundingdistance)
                        {
                            nearestboundingdistance = Bounds[i][dim];
                            nearestboundingindex = i;
                        }

                    }
                    double[] searchpoint = new double[dim];
                    for (int i = 0; i < dim; i++)
                    {
                        searchpoint[i] = Bounds[nearestboundingindex][i];
                        if (Bounds[nearestboundingindex][i] > center[i]) searchpoint[i] += 0.1F;
                        if (Bounds[nearestboundingindex][i] < center[i]) searchpoint[i] -= 0.1F;

                    }

                    ActualCell = this.LookupIndex(searchpoint);
                    BoundPointsCompleted.Add(Bounds[nearestboundingindex]);
                    Bounds.RemoveAt(nearestboundingindex);
                    //if already contains it continue with the next point
                    if (!Leaves.Contains(ActualCell)) foundnewBox = true;
                }



                //the points which are nearer than the nearest bound should be returned..
                while (returnedcount < count && nearestpointsdistance[returnedcount] < nearestboundingdistance)
                {

                    if (Microsoft.SqlServer.Server.SqlContext.IsAvailable)
                    {
                        result.Add(new KeyValuePair<long, double>(nearestpoints[returnedcount], nearestpointsdistance[returnedcount]));

                        //yield return new KeyValuePair<long, double>(nearestpoints[returnedcount], nearestpointsdistance[returnedcount]);
                    }
                    else
                    {
                        Core.Log(string.Format("{0,8} id,    {1,8} distance", nearestpoints[returnedcount], Math.Sqrt(nearestpointsdistance[returnedcount]).ToString(System.Globalization.CultureInfo.InvariantCulture.NumberFormat)));
                    }
                    returnedcount++;
                }
            }
            return result;
        }


        private IEnumerable PointQueryYieldReturn(double[] center, int count, double radius, string columns, string where)
        {
            //how many row returned?
            int returnedcount = 0;

            int dim = this.Dimensions;
            string distancecolumn = "";
            for (int i = 0; i < dim; i++)
            {
                if (i != 0) distancecolumn += "+";
                distancecolumn += ("((" + this.DataColumnNameForSelect(i) + "-(" + center[i].ToString(System.Globalization.CultureInfo.InvariantCulture.NumberFormat) + "))*(" + this.DataColumnNameForSelect(i) + "-(" + center[i].ToString(System.Globalization.CultureInfo.InvariantCulture.NumberFormat) + ")))");
            }
            //the Kd tree cells (ids) which have been queried already from the sql server
            System.Collections.ArrayList Leaves = new System.Collections.ArrayList();

            //The actual nearest vectors, (ids)
            long[] nearestpoints = new long[count];
            //The actual nearest vectors, (distance)
            double[] nearestpointsdistance = new double[count];
            for (int k = 0; k < count; k++)
            {
                nearestpoints[k] = 0;
                nearestpointsdistance[k] = double.MaxValue;
            }

            //The actual farest point distance
            double farestpointdistance = double.MaxValue;

            //The index of this point
            int farestpointindex = 0;

            //The actual nearest bounding box point
            double nearestboundingdistance = 0;

            //The index of this point
            int nearestboundingindex = 0;

            //The actual Bounding points
            //empty at start
            //key is the distance from the start point
            List<double[]> Bounds = new List<double[]>();

            int numberofRows = 0;



            //The points which completed, no need to register them again..
            List<double[]> BoundPointsCompleted = new List<double[]>();
            BoundPointsCompleted.Add(center);

            //First determine in which cells the point belongs to
            int ActualCell = this.LookupIndex(center);


            long queriedpoint;
            double queriedpointdistance;

            Boolean readsucc = false;
            if (radius == 0)
            {

                farestpointdistance = nearestpointsdistance[count - 1];
            }
            else
            {
                farestpointdistance = radius * radius;
            }

            while (farestpointdistance > nearestboundingdistance)
            {

                farestpointindex = 0;

                //cellread++;
                //Add new Cell
                //Read points from DB
                string sql = Elte.SqlSpIdx.Indices.BalancedTree.BalancedTreeScripts.KD_QueryBox;

                sql = sql.Replace("$columns", ((count == 0) ? "" : (" top " + count.ToString())) + " " + columns);
                sql = sql.Replace("$Table", this.Table);
                sql = sql.Replace("$Indexname", this.IndexColumn);
                sql = sql.Replace("$cellnumber", ActualCell.ToString());
                if (where == "NN")
                {
                    if (farestpointdistance != double.MaxValue)
                    {
                        sql += " and " + distancecolumn + " < " + farestpointdistance.ToString(System.Globalization.CultureInfo.InvariantCulture.NumberFormat);
                    }
                    sql += " order by " + distancecolumn;
                }
                else
                {
                    sql += where;
                }


                using (SqlCommand cmd = new SqlCommand(sql, this.databaseConnection))
                {
                    using (SqlDataReader r = cmd.ExecuteReader())
                    {
                        readsucc = r.Read();
                        while (readsucc)
                        {
                            numberofRows++;
                            queriedpointdistance = (double)r[0];
                            if (queriedpointdistance < farestpointdistance)
                            {
                                if (count != 0)
                                {
                                    queriedpoint = (long)r[1];
                                    while (farestpointindex < count && nearestpointsdistance[farestpointindex] <= queriedpointdistance)
                                    {
                                        farestpointindex++;
                                    }
                                    if (farestpointindex >= count)
                                    {
                                        farestpointdistance = nearestpointsdistance[count - 1];
                                        break;
                                    }
                                    for (int i = count - 1; i > farestpointindex; i--)
                                    {
                                        nearestpoints[i] = nearestpoints[i - 1];
                                        nearestpointsdistance[i] = nearestpointsdistance[i - 1];
                                    }
                                    nearestpoints[farestpointindex] = queriedpoint;
                                    nearestpointsdistance[farestpointindex] = queriedpointdistance;
                                    farestpointindex++;
                                    farestpointdistance = nearestpointsdistance[count - 1];
                                    if (farestpointindex >= count)
                                    {
                                        break;
                                    }
                                }
                                else
                                {
                                    if (Microsoft.SqlServer.Server.SqlContext.IsAvailable)
                                    {
                                        yield return r;
                                        //yield return new KeyValuePair<long, double>(queriedpoint, queriedpointdistance);
                                    }
                                    else
                                    {
                                        object[] values = new object[r.FieldCount];
                                        r.GetValues(values);
                                        for (int k = 0; k < r.FieldCount; k++)
                                        {
                                            if (values[k] != null)
                                            {
                                                Console.Write(values[k].ToString());
                                                Console.Write(",");
                                            }
                                        }

                                    }


                                }
                            }
                            readsucc = r.Read();
                        }

                    }

                }

                Leaves.Add(ActualCell);
                //add the bounding points of it
                bool contains = false;
                foreach (double[] actualpoint in this.GetBoxPoints(ActualCell, center))
                {
                    //Previous point?
                    foreach (double[] boundpoint in BoundPointsCompleted)
                    {
                        contains = true;
                        for (int i = 0; i < dim; i += 1)
                        {
                            if (boundpoint[i] != actualpoint[i])
                            {
                                contains = false;
                                break;
                            }
                        }
                        if (contains) break;
                    }
                    //Already in actual list?
                    if (!contains)
                    {
                        foreach (double[] boundpoint in Bounds)
                        {
                            contains = true;
                            for (int i = 0; i < dim; i += 1)
                            {
                                if (boundpoint[i] != actualpoint[i])
                                {
                                    contains = false;
                                    break;
                                }
                            }
                            if (contains) break;
                        }
                    }
                    if (!contains)
                    {
                        Bounds.Add(actualpoint);
                    }
                }
                //int[]  Clls = this.GetLeavesInvolved(dim, Bounds,center);
                Boolean foundnewBox = false;
                while (!foundnewBox && Bounds.Count > 0)
                {
                    //Search for the nearest bounding box point
                    //double nearestboundingdistance = 0;

                    //The index of this point
                    //int nearestboundingindex = 0;
                    nearestboundingdistance = double.MaxValue;
                    nearestboundingindex = 0;
                    for (int i = 0; i < Bounds.Count; i++)
                    {
                        if (Bounds[i][dim] < nearestboundingdistance)
                        {
                            nearestboundingdistance = Bounds[i][dim];
                            nearestboundingindex = i;
                        }

                    }
                    double[] searchpoint = new double[dim];
                    for (int i = 0; i < dim; i++)
                    {
                        searchpoint[i] = Bounds[nearestboundingindex][i];
                        if (Bounds[nearestboundingindex][i] > center[i]) searchpoint[i] += 0.1F;
                        if (Bounds[nearestboundingindex][i] < center[i]) searchpoint[i] -= 0.1F;

                    }

                    ActualCell = this.LookupIndex(searchpoint);
                    BoundPointsCompleted.Add(Bounds[nearestboundingindex]);
                    Bounds.RemoveAt(nearestboundingindex);
                    //if already contains it continue with the next point
                    if (!Leaves.Contains(ActualCell)) foundnewBox = true;
                }



                //the points which are nearer than the nearest bound should be returned..
                while (returnedcount < count && nearestpointsdistance[returnedcount] < nearestboundingdistance)
                {

                    if (Microsoft.SqlServer.Server.SqlContext.IsAvailable)
                    {
                        yield return new KeyValuePair<long, double>(nearestpoints[returnedcount], nearestpointsdistance[returnedcount]);
                    }
                    else
                    {
                        Core.Log(string.Format("{0,8} id,    {1,8} distance", nearestpoints[returnedcount], Math.Sqrt(nearestpointsdistance[returnedcount]).ToString(System.Globalization.CultureInfo.InvariantCulture.NumberFormat)));
                    }
                    returnedcount++;
                }
            }

        }



        public IEnumerable NearestNeighborsIndexold(SqlBinary coord, SqlInt32 N1)
        {
            double[] center = Converters.VectorConverter.ToArray(coord);

            int N = (int)N1;

            if (this.DatabaseConnection.State != ConnectionState.Open)
                this.DatabaseConnection.Open();
            int count = (int)N;

            int dim = this.Dimensions;

            //the Kd tree cells (ids) which have been queried already from the sql server
            System.Collections.ArrayList Leaves = new System.Collections.ArrayList();

            //The actual nearest vectors, (ids)
            long[] nearestpoints = new long[count];
            //The actual nearest vectors, (distance)
            double[] nearestpointsdistance = new double[count];
            for (int k = 0; k < count; k++)
            {
                nearestpoints[k] = 0;
                nearestpointsdistance[k] = double.MaxValue;
            }

            //The actual farest point distance
            double farestpointdistance = double.MaxValue;

            //The index of this point
            int farestpointindex = 0;

            //The actual nearest bounding box point
            double nearestboundingdistance = 0;

            //The index of this point
            int nearestboundingindex = 0;

            //The actual Bounding points
            //empty at start
            //key is the distance from the start point
            List<double[]> Bounds = new List<double[]>();

            int numberofRows = 0;



            //The points which completed, no need to register them again..
            List<double[]> BoundPointsCompleted = new List<double[]>();
            BoundPointsCompleted.Add(center);

            //First determine in which cells the point belongs to
            int ActualCell = this.LookupIndex(center);

            string columns = "";

            columns += this.PrimaryKeyColumn;
            columns += ",";


            //add the distance (square) to the columns
            string distancecolumn = "";
            for (int i = 0; i < dim; i++)
            {
                if (i != 0) distancecolumn += "+";
                distancecolumn += ("((" + this.DataColumnNameForSelect(i) + "-(" + center[i].ToString(System.Globalization.CultureInfo.InvariantCulture.NumberFormat) + "))*(" + this.DataColumnNameForSelect(i) + "-(" + center[i].ToString(System.Globalization.CultureInfo.InvariantCulture.NumberFormat) + ")))");
            }
            //if (this.BinaryDataColumn)
            //{
            //    distancecolumn = " dbo.VectorDistance(" + this.DataColumn + "," + this.DataColumn + ")";
            //}
            columns += distancecolumn;


            long queriedpoint;
            double queriedpointdistance;

            Boolean readsucc = false;

            farestpointdistance = nearestpointsdistance[count - 1];

            while (farestpointdistance > nearestboundingdistance)
            {
                farestpointindex = 0;

                //cellread++;
                //Add new Cell
                //Read points from DB
                string sql = Elte.SqlSpIdx.Indices.BalancedTree.BalancedTreeScripts.KD_QueryBox;

                sql = sql.Replace("$columns", " top " + count.ToString() + " " + columns);
                sql = sql.Replace("$Table", this.Table);
                sql = sql.Replace("$Indexname", this.IndexColumn);
                sql = sql.Replace("$cellnumber", ActualCell.ToString());
                if (farestpointdistance != double.MaxValue)
                    sql += " and " + distancecolumn + " < " + farestpointdistance.ToString(System.Globalization.CultureInfo.InvariantCulture.NumberFormat);
                sql += " order by " + distancecolumn;

                using (SqlCommand cmd = new SqlCommand(sql, this.databaseConnection))
                {
                    using (SqlDataReader r = cmd.ExecuteReader())
                    {
                        readsucc = r.Read();
                        while (readsucc)
                        {
                            numberofRows++;
                            queriedpointdistance = (double)r[1];
                            if (queriedpointdistance < farestpointdistance)
                            {


                                queriedpoint = (long)r[0];


                                while (farestpointindex < count && nearestpointsdistance[farestpointindex] <= queriedpointdistance)
                                {
                                    farestpointindex++;
                                }
                                if (farestpointindex >= count)
                                {
                                    farestpointdistance = nearestpointsdistance[count - 1];
                                    break;
                                }
                                for (int i = count - 1; i > farestpointindex; i--)
                                {
                                    nearestpoints[i] = nearestpoints[i - 1];
                                    nearestpointsdistance[i] = nearestpointsdistance[i - 1];
                                }
                                nearestpoints[farestpointindex] = queriedpoint;
                                nearestpointsdistance[farestpointindex] = queriedpointdistance;
                                farestpointindex++;
                                farestpointdistance = nearestpointsdistance[count - 1];
                                if (farestpointindex >= count)
                                {
                                    break;
                                }
                            }

                            readsucc = r.Read();

                        }

                    }

                }

                Leaves.Add(ActualCell);
                //add the bounding points of it
                bool contains = false;
                foreach (double[] actualpoint in this.GetBoxPoints(ActualCell, center))
                {
                    //Previous point?
                    foreach (double[] boundpoint in BoundPointsCompleted)
                    {
                        contains = true;
                        for (int i = 0; i < dim; i += 1)
                        {
                            if (boundpoint[i] != actualpoint[i])
                            {
                                contains = false;
                                break;
                            }
                        }
                        if (contains) break;
                    }
                    //Already in actual list?
                    if (!contains)
                    {
                        foreach (double[] boundpoint in Bounds)
                        {
                            contains = true;
                            for (int i = 0; i < dim; i += 1)
                            {
                                if (boundpoint[i] != actualpoint[i])
                                {
                                    contains = false;
                                    break;
                                }
                            }
                            if (contains) break;
                        }
                    }
                    if (!contains)
                    {
                        Bounds.Add(actualpoint);
                    }
                }
                //int[]  Clls = this.GetLeavesInvolved(dim, Bounds,center);
                Boolean foundnewBox = false;
                while (!foundnewBox && Bounds.Count > 0)
                {
                    //Search for the nearest bounding box point
                    //double nearestboundingdistance = 0;

                    //The index of this point
                    //int nearestboundingindex = 0;
                    nearestboundingdistance = double.MaxValue;
                    nearestboundingindex = 0;
                    for (int i = 0; i < Bounds.Count; i++)
                    {
                        if (Bounds[i][dim] < nearestboundingdistance)
                        {
                            nearestboundingdistance = Bounds[i][dim];
                            nearestboundingindex = i;
                        }

                    }
                    double[] searchpoint = new double[dim];
                    for (int i = 0; i < dim; i++)
                    {
                        searchpoint[i] = Bounds[nearestboundingindex][i];
                        if (Bounds[nearestboundingindex][i] > center[i]) searchpoint[i] += 0.1F;
                        if (Bounds[nearestboundingindex][i] < center[i]) searchpoint[i] -= 0.1F;

                    }

                    ActualCell = this.LookupIndex(searchpoint);
                    BoundPointsCompleted.Add(Bounds[nearestboundingindex]);
                    Bounds.RemoveAt(nearestboundingindex);
                    //if already contains it continue with the next point
                    if (!Leaves.Contains(ActualCell)) foundnewBox = true;
                }

            }
            string vector = "";
            if (Microsoft.SqlServer.Server.SqlContext.IsAvailable)
            {

                ////return record structure
                //SqlDataRecord record = new SqlDataRecord(
                //            new SqlMetaData("vector", SqlDbType.VarChar, 50),
                //            new SqlMetaData("Dist", SqlDbType.Float));
                ////Send the results
                //SqlContext.Pipe.SendResultsStart(record);



                //for (int i = 0; i < count; i++)
                //{
                //    vector = "";
                //    for (int k = 0; k < dim; k++)
                //    {
                //        vector += nearestpoints[i][k].ToString() + ",";
                //    }
                //    record.SetSqlString(0, vector);
                //    record.SetDouble(1, Math.Sqrt(nearestpoints[i][dim]));
                //    SqlContext.Pipe.SendResultsRow(record);
                //}
                //SqlContext.Pipe.SendResultsEnd();
            }
            else
            {
                for (int i = 0; i < count; i++)
                {
                    Core.Log(string.Format("{0,8} id,    {1,8} distance", nearestpoints[i], Math.Sqrt(nearestpointsdistance[i]).ToString(System.Globalization.CultureInfo.InvariantCulture.NumberFormat)));
                }

            }
            for (int i = 0; i < count; i++)
            {

                yield return (long)i;

            }


        }




        /// <summary>
        /// Search for the box of a given point
        /// </summary>
        /// <param name="P">point given in a double array</param>
        /// <returns>box id</returns>
        public override int LookupIndex(double[] coord)
        {
            this.LoadBoxes();
            int actualBox = 1;
            for (int actualstage = 0; actualstage < this.TreeDepth; actualstage++)
            {
                actualBox *= 2;
                for (int k = 0; k < Dimensions; k++)
                {
                    //min or max
                    if ((BoundingBoxes[actualBox][k * 2] > coord[k]) || (BoundingBoxes[actualBox][k * 2 + 1] <= coord[k]))
                    {
                        actualBox++;
                        break;
                    }
                }
            }
            return actualBox;
        }



        #endregion




        #region "oldcodes"
        ///// <summary>
        ///// Returns a histogram estimated upon the KD-tree
        ///// </summary>
        ///// <param name="name">The kd tree index to use </param>
        ///// <param name="resolution">Resolution of the histogram eg: 0.1</param>
        ///// <param name="userandom">If true, method randomly distribute the points in each cell (slower)</param>
        ///// <returns>returns as a select results, each row contains, the cell coordinates, and the number of points in it
        ///// only cells with at least one point returned
        ///// </returns>
        //[Microsoft.SqlServer.Server.SqlProcedure]
        //public static void HistogramEstimation(SqlString name, SqlDouble resolution, SqlInt32 userandom)
        //{
        //    Random r = new Random();

        //    //Get the index by name
        //    BalancedTreeIndex index = GetIndex(name);

        //    int dim = index.Dimensions;
        //    index.DatabaseConnection = new SqlConnection(Constants.MyConnectionString);
        //    Core.Log("Resolution : " + resolution.ToString());




        //    //Determine the cellnumbers for histogram
        //    int[] cellsinrow = new int[2];
        //    int[] min = new int[2];
        //    int[] max = new int[2];
        //    for (int i = 0; i < 2; i++)
        //    {
        //        max[i] = (int)(index.max[i] / resolution);
        //        min[i] = (int)(index.min[i] / resolution) + 1;
        //        cellsinrow[i] = max[i] - min[i];
        //    }

        //    //memory array for the results
        //    Int64[,] results = new Int64[cellsinrow[0], cellsinrow[1]];

        //    //the number of points in one box
        //    int numberofpoints = index.rows / index.LeafBoxes.Count;



        //    int x, y;
        //    //for each LeafBoxes
        //    foreach (double[] box in index.LeafBoxes)
        //    {
        //        if (userandom == 0)
        //        {

        //            x = (int)(((box[0] + box[1]) / 2) / resolution) - min[0];
        //            y = (int)(((box[2] + box[3]) / 2) / resolution) - min[1];
        //            if (x < 0) x = 0;
        //            if (y < 0) y = 0;
        //            if (x >= cellsinrow[0]) x = cellsinrow[0] - 1;
        //            if (y >= cellsinrow[1]) y = cellsinrow[1] - 1;

        //            //determine the cell and increase it with the number of points in the box
        //            results[
        //                x,
        //                y
        //                ] += numberofpoints;
        //        }
        //        else
        //        {
        //            double xdiff = box[1] - box[0];
        //            double x0 = box[0];
        //            double ydiff = box[3] - box[2];
        //            double y0 = box[2];
        //            for (int i = 0; i < numberofpoints; i++)
        //            {


        //                x = (int)((x0 + r.NextDouble() * xdiff) / resolution) - min[0];
        //                y = (int)((y0 + r.NextDouble() * ydiff) / resolution) - min[1];
        //                if (x < 0) x = 0;
        //                if (y < 0) y = 0;
        //                if (x >= cellsinrow[0]) x = cellsinrow[0] - 1;
        //                if (y >= cellsinrow[1]) y = cellsinrow[1] - 1;

        //                //determine the cell and increase it with one point
        //                results[
        //                    x,
        //                    y
        //                    ]++;

        //            }
        //        }
        //    }





        //    //return record structure
        //    if (Microsoft.SqlServer.Server.SqlContext.IsAvailable)
        //    {
        //        SqlDataRecord record = new SqlDataRecord(
        //           new SqlMetaData(index.dataColumnNamesArray[0], SqlDbType.Int),
        //        new SqlMetaData(index.dataColumnNamesArray[1], SqlDbType.Int),
        //            new SqlMetaData("Number_of_points", SqlDbType.BigInt)
        //            );

        //        SqlContext.Pipe.SendResultsStart(record);
        //        for (int i = 0; i < cellsinrow[0]; i++)
        //        {
        //            for (int k = 0; k < cellsinrow[1]; k++)
        //            {
        //                if (results[i, k] != 0)
        //                {
        //                    record.SetInt32(0, i + min[0]);
        //                    record.SetInt32(1, k + min[1]);
        //                    record.SetInt64(2, results[i, k]);
        //                    SqlContext.Pipe.SendResultsRow(record);
        //                }
        //            }
        //        }
        //        SqlContext.Pipe.SendResultsEnd();
        //    }
        //    else
        //    {
        //        for (int i = 0; i < cellsinrow[0]; i++)
        //        {
        //            for (int k = 0; k < cellsinrow[1]; k++)
        //            {
        //                if (results[i, k] != 0)
        //                {
        //                    Core.Log("X>{0,8}    Y>{1,8}   N:{2,8}", i + min[0], k + min[1], results[i, k]);
        //                }
        //            }
        //        }
        //    }

        //}


        /// <summary>
        /// Estimate a given points value using its N nearest neighbour
        /// </summary>
        /// <param name="name">the kd-tree index to use</param>
        /// <param name="coord">the coordinates of point to estimate</param>
        /// <param name="count">set the N (how many neighbours to use)</param>
        /// <param name="valuecolumnname">the column name which contains the value to estimate</param>
        public IEnumerable<double[]> EstimateValueBalancedTree(SqlString name, SqlBinary coord, int count, string valuecolumnname)
        {
            //Get the this by name


            int dim = this.Dimensions;

            //Number of cell read from SQL server
            int cellread = 0;

            //The point which neighbors we searching
            double[] center = Converters.VectorConverter.ToArray(coord.Value);

            //the Kd tree cells (ids) which have been queried already from the sql server
            System.Collections.ArrayList Leaves = new System.Collections.ArrayList();

            //The actual nearest vectors,
            double[][] nearestpoints = new double[count][];
            for (int k = 0; k < count; k++)
            {
                nearestpoints[k] = new double[dim + 1];
                nearestpoints[k][dim] = double.MaxValue;
            }
            //The actual farest point distance
            double farestpointdistance = double.MaxValue;

            //The this of this point
            int farestpointthis = 0;


            //The actual nearest bounding box point
            double nearestboundingdistance = 0;

            //The this of this point
            int nearestboundingthis = 0;

            //The actual Bounding points
            //empty at start
            //key is the distance from the start point
            List<double[]> Bounds = new List<double[]>();

            int numberofRows = 0;

            double temp = 0;
            //The points which completed, no need to register them again..
            List<double[]> BoundPointsCompleted = new List<double[]>();
            BoundPointsCompleted.Add(center);

            //First determine in which cells the point belongs to
            int ActualCell = this.LookupIndex(center);

            string columns = "";
            for (int i = 0; i < dim; i++)
            {

                columns += this.dataColumnNamesArray[i];
                columns += ",";
            }

            string distancecolumn = "";
            for (int i = 0; i < dim; i++)
            {
                if (i != 0) distancecolumn += "+";
                distancecolumn += ("((" + this.dataColumnNamesArray[i] + "-(" + center[i].ToString() + "))*(" + this.dataColumnNamesArray[i] + "-(" + center[i].ToString() + ")))");
            }
            columns += distancecolumn;
            columns += ", " + valuecolumnname;
            double[] queriedpoint;

            Boolean readsucc = false;

            farestpointdistance = nearestpoints[count - 1][dim];


            while (farestpointdistance > nearestboundingdistance)
            {


                farestpointthis = 0;

                cellread++;
                //Add new Cell
                //Read points from DB
                string sql = Elte.SqlSpIdx.Indices.BalancedTree.BalancedTreeScripts.KD_QueryBox;

                sql = sql.Replace("$columns", " top " + count.ToString() + " " + columns);
                sql = sql.Replace("$Table", this.Table);
                sql = sql.Replace("[$Indexname]", this.IndexColumn);
                sql = sql.Replace("$thisname", this.IndexColumn);
                sql = sql.Replace("$cellnumber", ActualCell.ToString());
                if (farestpointdistance != double.MaxValue)
                    sql += " and " + distancecolumn + " < " + farestpointdistance.ToString(System.Globalization.CultureInfo.InvariantCulture.NumberFormat);
                sql += " order by " + distancecolumn;

                using (SqlCommand cmd = new SqlCommand(sql, this.DatabaseConnection))
                {



                    using (SqlDataReader r = cmd.ExecuteReader())
                    {


                        readsucc = r.Read();

                        while (readsucc)
                        {
                            numberofRows++;
                            temp = (double)r[dim];

                            if (temp < farestpointdistance)
                            {
                                queriedpoint = new double[dim + 2];
                                for (int i = 0; i < dim; i++)
                                {
                                    queriedpoint[i] = (double)r[i];
                                }
                                queriedpoint[dim + 1] = (double)r[dim + 1];
                                queriedpoint[dim] = temp;



                                while (farestpointthis < count && nearestpoints[farestpointthis][dim] <= temp)
                                {
                                    farestpointthis++;
                                }
                                if (farestpointthis >= count)
                                {
                                    farestpointdistance = nearestpoints[count - 1][dim];
                                    break;
                                }
                                for (int i = count - 1; i > farestpointthis; i--)
                                {
                                    nearestpoints[i] = nearestpoints[i - 1];

                                }
                                nearestpoints[farestpointthis] = queriedpoint;

                                farestpointthis++;
                                farestpointdistance = nearestpoints[count - 1][dim];
                                if (farestpointthis >= count)
                                {
                                    break;
                                }
                            }


                            readsucc = r.Read();

                        }

                    }

                }


                Leaves.Add(ActualCell);
                //add the bounding points of it
                bool contains = false;
                foreach (double[] actualpoint in this.GetBoxPoints(ActualCell, center))
                {
                    //Previous point?
                    foreach (double[] boundpoint in BoundPointsCompleted)
                    {
                        contains = true;
                        for (int i = 0; i < dim; i += 1)
                        {
                            if (boundpoint[i] != actualpoint[i])
                            {
                                contains = false;
                                break;
                            }
                        }
                        if (contains) break;
                    }
                    //Already in actual list?
                    if (!contains)
                    {
                        foreach (double[] boundpoint in Bounds)
                        {
                            contains = true;
                            for (int i = 0; i < dim; i += 1)
                            {
                                if (boundpoint[i] != actualpoint[i])
                                {
                                    contains = false;
                                    break;
                                }
                            }
                            if (contains) break;
                        }
                    }
                    if (!contains)
                    {
                        Bounds.Add(actualpoint);
                    }
                }
                //int[]  Clls = this.GetLeavesInvolved(dim, Bounds,center);

                Boolean foundnewBox = false;
                while (!foundnewBox && Bounds.Count > 0)
                {
                    //Search for the nearest bounding box point
                    //double nearestboundingdistance = 0;

                    //The distance of this point
                    //int nearestboundingthis = 0;
                    nearestboundingdistance = double.MaxValue;
                    nearestboundingthis = 0;
                    for (int i = 0; i < Bounds.Count; i++)
                    {
                        if (Bounds[i][dim] < nearestboundingdistance)
                        {
                            nearestboundingdistance = Bounds[i][dim];
                            nearestboundingthis = i;
                        }

                    }
                    double[] searchpoint = new double[dim];
                    for (int i = 0; i < dim; i++)
                    {
                        searchpoint[i] = Bounds[nearestboundingthis][i];
                        if (Bounds[nearestboundingthis][i] > center[i]) searchpoint[i] += 0.1F;
                        if (Bounds[nearestboundingthis][i] < center[i]) searchpoint[i] -= 0.1F;

                    }

                    ActualCell = this.LookupIndex(searchpoint);
                    BoundPointsCompleted.Add(Bounds[nearestboundingthis]);
                    Bounds.RemoveAt(nearestboundingthis);
                    //if already contains it continue with the next point
                    if (!Leaves.Contains(ActualCell)) foundnewBox = true;
                }

            }
            this.DatabaseConnection.Close();

            double[][] Rx = new double[count][];
            double[] Ry = new double[count];
            double[] Rd = new double[count];
            int actuali = 0;
            for (int i = 0; i < count; i++)
            {
                //leave it if it is the center point...
                if (nearestpoints[i][dim] != 0)
                {
                    Rx[actuali] = new double[dim];
                    for (int k = 0; k < dim; k++)
                    {
                        Rx[actuali][k] = nearestpoints[i][k];
                    }
                    //value
                    Ry[actuali] = nearestpoints[i][dim + 1];
                    //distance
                    Rd[actuali] = nearestpoints[i][dim];
                    actuali++;
                }
            }
            double[] result = new double[4];
            List<double[]> resultlist = new List<double[]>();
            double[] Rx0 = new double[dim];
            for (int k = 0; k < dim; k++)
            {
                Rx0[k] = center[k];
            }
            double avgR = 0;
            double avg = 0;
            double weight = 0;
            for (int k = 0; k < count; k++)
            {
                avgR += Ry[k] * 1 / Rd[k];
                avg += Ry[k];
                weight += 1 / Rd[k];
            }
            avgR /= weight;
            avg /= count;

            //NR
            result[0] = 0;// (double)NumericalRecipes.NR_Redshift.Estimate_Value(Rx, Ry, Rd, Rx0);

            //AVG
            result[1] = (double)avg;

            //Avg with 1/distance weight
            result[2] = (double)avgR;

            //Avg with 1/(distance add 2) weight

            avgR = 0;
            weight = 0;
            for (int k = 0; k < count; k++)
            {
                avgR += Ry[k] * 1 / (Rd[k] * Rd[k]);

                weight += 1 / (Rd[k] * Rd[k]);
            }
            avgR /= weight;
            result[3] = avgR;


            //if (Microsoft.SqlServer.Server.SqlContext.IsAvailable)
            //{
            //    //return record structure
            //    SqlDataRecord record = new SqlDataRecord(

            //                new SqlMetaData("Estimated", SqlDbType.Float),
            //                new SqlMetaData("AVG", SqlDbType.Float)
            //    );

            //    SqlContext.Pipe.SendResultsStart(record);

            //    record.SetDouble(0, RRedshift);
            //    record.SetDouble(1, avgR);
            //    SqlContext.Pipe.SendResultsRow(record);
            //    SqlContext.Pipe.SendResultsEnd();
            //}
            //else
            //{
            //    Core.Log("NR estimate> {1,7} ,Avarage> {2,7}", RRedshift, avgR);
            //} 
            for (int i = 0; i < 10; i++)
            {


                yield return result;
            }

        }

        ///// <summary>
        ///// Estimate a given points value using its N nearest neighbour
        ///// uses a SQL select for its input
        ///// <note>Near the same as EstimateValueBalancedTree, but this can be used for multiple points</note>
        ///// </summary>
        ///// <param name="name">the kd-tree index to use</param>
        ///// <param name="EstimatePointsSQL">valid sql select which return points to estimate</param>
        ///// <param name="count">set the N (how many neighbours to use)</param>
        ///// <param name="valuecolumnname">the column name which contains the value to estimate</param>
        ///// <param name="frommemory">determinates wheter to use the boundingboxes from memory or from SQL table for each point
        ///// <note>for large number of points it is faster to once read all the boxes to the memory, of course it has limitations in size</note></param>
        //public static void EstimateValueBalancedTreeUsingSelect(string name, string EstimatePointsSQL, int count, string valuecolumnname, bool frommemory)
        //{
        //    //Total time
        //    System.Diagnostics.Stopwatch totaltime = new System.Diagnostics.Stopwatch();
        //    System.Diagnostics.Stopwatch Alltime = new System.Diagnostics.Stopwatch();

        //    //Get the index by name
        //    BalancedTreeIndex index = GetIndex(name);


        //    int dim = index.Dimensions;

        //    //Number of cell read from SQL server
        //    int cellread = 0;
        //    object record = new object();
        //    if (Microsoft.SqlServer.Server.SqlContext.IsAvailable)
        //    {
        //        record = new SqlDataRecord(
        //                         new SqlMetaData("Original", SqlDbType.Float),
        //                         new SqlMetaData("Estimated", SqlDbType.Float),
        //                         new SqlMetaData("AVG", SqlDbType.Float),
        //                         new SqlMetaData("Nearest R", SqlDbType.Float),
        //                         new SqlMetaData("Nearest 2nd R", SqlDbType.Float),
        //                         new SqlMetaData("N", SqlDbType.Int),
        //                         new SqlMetaData("Cells", SqlDbType.Int)
        //             );
        //        SqlContext.Pipe.SendResultsStart((SqlDataRecord)record);
        //    }
        //    Queue<double[]> Points = new Queue<double[]>();
        //    double[] QPoint;

        //    using (SqlConnection cn = new SqlConnection(Constants.MyConnectionString))
        //    {
        //        cn.Open();

        //        using (SqlCommand cmd = new SqlCommand(EstimatePointsSQL, cn))
        //        {
        //            using (SqlDataReader r = cmd.ExecuteReader())
        //            {
        //                bool readsucc = r.Read();
        //                while (readsucc)
        //                {
        //                    QPoint = new double[dim + 1];
        //                    for (int i = 0; i < dim + 1; i++)
        //                    {
        //                        QPoint[i] = (double)r[i];
        //                    }
        //                    Points.Enqueue(QPoint);
        //                    readsucc = r.Read();
        //                }
        //            }
        //        }
        //        Alltime.Start();

        //        double[] queriedpoint;
        //        string columns = "";
        //        for (int i = 0; i < dim; i++)
        //        {
        //            columns += index.dataColumnNamesArray[i];
        //            if (i != dim - 1) columns += ",";
        //        }

        //        if (frommemory)
        //        {
        //            int actualleaf = 0;
        //            if (memTable != index.Name)
        //            {

        //                Core.Log("LoadToMemory");
        //                leaffrom = new int[(int)Math.Pow(2, index.TreeDepth + 1) + 1];
        //                leafto = new int[(int)Math.Pow(2, index.TreeDepth + 1) + 1];
        //                string sql = "select count(*) from $Table ";
        //                sql = sql.Replace("$Table", index.Table);
        //                using (SqlCommand cmd = new SqlCommand(sql, cn))
        //                {
        //                    using (SqlDataReader r = cmd.ExecuteReader())
        //                    {
        //                        r.Read();
        //                        memPoints = new double[(int)r[0]][];
        //                    }
        //                }

        //                sql = "select $columns,$Indexname,$valuecolumnname from $Table order by $Indexname";
        //                sql = sql.Replace("$columns", columns);
        //                sql = sql.Replace("$Table", index.Table);
        //                sql = sql.Replace("$Indexname", index.IndexColumn);
        //                sql = sql.Replace("$valuecolumnname", valuecolumnname);

        //                totaltime.Start();
        //                int i = 0;
        //                using (SqlCommand cmd = new SqlCommand(sql, cn))
        //                {
        //                    using (SqlDataReader r = cmd.ExecuteReader())
        //                    {
        //                        while (r.Read())
        //                        {
        //                            queriedpoint = new double[dim + 1];
        //                            for (int k = 0; k < dim; k++)
        //                            {
        //                                queriedpoint[k] = (double)r[k];
        //                            }
        //                            queriedpoint[dim] = (double)r[dim + 1];
        //                            memPoints[i] = queriedpoint;
        //                            if (actualleaf != (int)r[dim])
        //                            {
        //                                leafto[actualleaf] = i - 1;
        //                                actualleaf = (int)r[dim];
        //                                leaffrom[actualleaf] = i;
        //                            }
        //                            i++;
        //                        }
        //                    }
        //                }
        //                memTable = index.Name;
        //            }
        //        }
        //        if (memTable != index.Name)
        //        {
        //            memTable = index.Name;
        //        }

        //        while (Points.Count > 0)
        //        {

        //            //The point which neighbors we searching
        //            double[] center = Points.Dequeue();

        //            //double[] center = new double[Converters.VectorConverter.ToArray(coord.Value).GetLength(0)];
        //            //Converters.VectorConverter.ToArray(coord.Value).CopyTo(center, 0);

        //            //the Kd tree cells (ids) which have been queried already from the sql server
        //            System.Collections.ArrayList Leaves = new System.Collections.ArrayList();

        //            //The actual nearest vectors,
        //            double[][] nearestpoints = new double[count][];
        //            for (int k = 0; k < count; k++)
        //            {
        //                nearestpoints[k] = new double[dim + 1];
        //                nearestpoints[k][dim] = double.MaxValue;
        //            }
        //            //The actual farest point distance
        //            double farestpointdistance = double.MaxValue;

        //            //The index of this point
        //            int farestpointindex = 0;

        //            //The actual nearest bounding box point
        //            double nearestboundingdistance = 0;

        //            //The actual Bounding points
        //            //empty at start
        //            //key is the distance from the start point
        //            SortedList<double, double[]> Bounds = new SortedList<double, double[]>();
        //            int numberofRows = 0;
        //            double temp = 0;
        //            //The points which completed, no need to register them again..
        //            List<double[]> BoundPointsCompleted = new List<double[]>();
        //            BoundPointsCompleted.Add(center);
        //            //First determine in which cells the point belongs to
        //            int ActualCell = index.LookupIndex(center);
        //            string distancecolumn = "";
        //            for (int i = 0; i < dim; i++)
        //            {
        //                if (i != 0) distancecolumn += "+";
        //                distancecolumn += ("((" + index.dataColumnNamesArray[i] + "-(" + center[i].ToString() + "))*(" + index.dataColumnNamesArray[i] + "-(" + center[i].ToString() + ")))");
        //            }
        //            columns += ",";
        //            columns += distancecolumn;
        //            columns += ", " + valuecolumnname;

        //            Boolean readsucc = false;
        //            farestpointdistance = nearestpoints[count - 1][dim];

        //            while (farestpointdistance > nearestboundingdistance)
        //            {
        //                farestpointindex = 0;
        //                cellread++;
        //                //Add new Cell
        //                if (frommemory)
        //                {
        //                    for (int leaves = leaffrom[ActualCell]; leaves <= leafto[ActualCell]; leaves++)
        //                    {
        //                        numberofRows++;
        //                        temp = GetDistance(center, memPoints[leaves], dim);
        //                        if (temp < farestpointdistance)
        //                        {
        //                            queriedpoint = new double[dim + 2];
        //                            for (int i = 0; i < dim; i++)
        //                            {
        //                                queriedpoint[i] = memPoints[leaves][i];
        //                            }
        //                            //redshift
        //                            queriedpoint[dim + 1] = memPoints[leaves][dim];
        //                            //distance
        //                            queriedpoint[dim] = temp;
        //                            farestpointindex = 0;
        //                            while (farestpointindex < count && nearestpoints[farestpointindex][dim] <= temp)
        //                            {
        //                                farestpointindex++;
        //                            }
        //                            if (farestpointindex >= count)
        //                            {
        //                                farestpointdistance = nearestpoints[count - 1][dim];
        //                                break;
        //                            }
        //                            for (int i = count - 1; i > farestpointindex; i--)
        //                            {
        //                                nearestpoints[i] = nearestpoints[i - 1];

        //                            }
        //                            nearestpoints[farestpointindex] = queriedpoint;

        //                            farestpointindex++;
        //                            farestpointdistance = nearestpoints[count - 1][dim];
        //                            if (farestpointindex >= count)
        //                            {
        //                                break;
        //                            }
        //                        }
        //                    }


        //                }
        //                else
        //                {
        //                    //Read points from DB
        //                    string sql = Elte.SqlSpIdx.Indices.BalancedTree.BalancedTreeScripts.KD_QueryBox;
        //                    sql = sql.Replace("$columns", " top " + count.ToString() + " " + columns);
        //                    sql = sql.Replace("$Table", index.Table);
        //                    sql = sql.Replace("$Indexname", index.IndexColumn);
        //                    sql = sql.Replace("$cellnumber", ActualCell.ToString());
        //                    if (farestpointdistance != double.MaxValue)
        //                        sql += " and " + distancecolumn + " < " + farestpointdistance.ToString();
        //                    sql += " order by " + distancecolumn;
        //                    totaltime.Start();
        //                    using (SqlCommand cmd = new SqlCommand(sql, cn))
        //                    {
        //                        using (SqlDataReader r = cmd.ExecuteReader())
        //                        {
        //                            totaltime.Stop();
        //                            readsucc = r.Read();
        //                            while (readsucc)
        //                            {
        //                                numberofRows++;
        //                                temp = (double)r[dim];
        //                                if (temp < farestpointdistance)
        //                                {
        //                                    queriedpoint = new double[dim + 2];
        //                                    for (int i = 0; i < dim; i++)
        //                                    {
        //                                        queriedpoint[i] = (double)r[i];
        //                                    }
        //                                    queriedpoint[dim + 1] = (double)r[dim + 1];
        //                                    queriedpoint[dim] = temp;
        //                                    while (farestpointindex < count && nearestpoints[farestpointindex][dim] <= temp)
        //                                    {
        //                                        farestpointindex++;
        //                                    }
        //                                    if (farestpointindex >= count)
        //                                    {
        //                                        farestpointdistance = nearestpoints[count - 1][dim];
        //                                        break;
        //                                    }
        //                                    for (int i = count - 1; i > farestpointindex; i--)
        //                                    {
        //                                        nearestpoints[i] = nearestpoints[i - 1];

        //                                    }
        //                                    nearestpoints[farestpointindex] = queriedpoint;
        //                                    farestpointindex++;
        //                                    farestpointdistance = nearestpoints[count - 1][dim];
        //                                    if (farestpointindex >= count)
        //                                    {
        //                                        break;
        //                                    }
        //                                }
        //                                readsucc = r.Read();
        //                            }
        //                        }
        //                    }
        //                }
        //                Leaves.Add(ActualCell);
        //                //add the bounding points of it
        //                bool contains = false;
        //                double ThisKey;
        //                foreach (double[] actualpoint in index.GetBoxPoints(ActualCell, center))
        //                {
        //                    ThisKey = actualpoint[dim];
        //                    if (farestpointdistance > ThisKey)
        //                    {
        //                        //Previous point?
        //                        foreach (double[] boundpoint in BoundPointsCompleted)
        //                        {
        //                            contains = false;
        //                            for (int i = 0; i < dim; i += 1)
        //                            {
        //                                if (boundpoint[i] != actualpoint[i])
        //                                {
        //                                    contains = false;
        //                                    break;
        //                                }
        //                            }
        //                            if (contains) break;
        //                        }
        //                        //Already in actual list?
        //                        if (!contains)
        //                        {
        //                            int Maxindex = Bounds.Count - 1;
        //                            int lowindex = 0;
        //                            int highindex = Maxindex;

        //                            int tempindex;
        //                            while (lowindex < highindex - 1)
        //                            {
        //                                tempindex = (lowindex + highindex) / 2;
        //                                if (Bounds.Keys[tempindex] > ThisKey)
        //                                {
        //                                    highindex = tempindex;
        //                                }
        //                                else
        //                                {
        //                                    lowindex = highindex;
        //                                }
        //                            }
        //                            if (highindex >= 0)
        //                            {
        //                                while (Maxindex >= highindex && Bounds.Keys[highindex] < ThisKey + 0.01)
        //                                {
        //                                    if (EqualdoubleArray(Bounds.Values[highindex], actualpoint, dim))
        //                                    {
        //                                        contains = true;
        //                                        break;
        //                                    }
        //                                    highindex++;
        //                                }
        //                                while (0 <= lowindex && Bounds.Keys[lowindex] > ThisKey - 0.01)
        //                                {
        //                                    if (EqualdoubleArray(Bounds.Values[lowindex], actualpoint, dim))
        //                                    {
        //                                        contains = true;
        //                                        break;
        //                                    }
        //                                    lowindex--;
        //                                }
        //                            }
        //                        }
        //                        if (!contains)
        //                        {
        //                            while (Bounds.ContainsKey(ThisKey))
        //                            {
        //                                ThisKey += 0.0001F;
        //                            }
        //                            Bounds.Add(ThisKey, actualpoint);
        //                        }
        //                    }
        //                }

        //                Boolean foundnewBox = false;
        //                while (!foundnewBox && Bounds.Count > 0)
        //                {
        //                    double[] searchpoint = new double[dim];
        //                    for (int i = 0; i < dim; i++)
        //                    {
        //                        searchpoint[i] = Bounds.Values[0][i];
        //                        if (Bounds.Values[0][i] > center[i]) searchpoint[i] += 0.1F;
        //                        if (Bounds.Values[0][i] < center[i]) searchpoint[i] -= 0.1F;

        //                    }
        //                    ActualCell = index.LookupIndex(searchpoint);
        //                    BoundPointsCompleted.Add(Bounds.Values[0]);
        //                    Bounds.RemoveAt(0);
        //                    //if already contains it continue with the next point
        //                    if (!Leaves.Contains(ActualCell)) foundnewBox = true;
        //                }
        //                if (Bounds.Count == 0) nearestboundingdistance = double.MaxValue; else nearestboundingdistance = Bounds.Keys[0];
        //            }

        //            double[][] Rx = new double[count][];
        //            double[] Ry = new double[count];
        //            double[] Rd = new double[count];
        //            int actuali = 0;
        //            for (int i = 0; i < count; i++)
        //            {
        //                //leave it if it is the center point...
        //                if (nearestpoints[i][dim] != 0)
        //                {
        //                    Rx[actuali] = new double[dim];
        //                    for (int k = 0; k < dim; k++)
        //                    {

        //                        Rx[actuali][k] = nearestpoints[i][k];
        //                    }
        //                    //value
        //                    Ry[actuali] = nearestpoints[i][dim + 1];
        //                    //distance
        //                    Rd[actuali] = 1;//Math.Sqrt( nearestpoints[i][dim]);
        //                    actuali++;
        //                }
        //            }
        //            double[] Rx0 = new double[dim];
        //            for (int k = 0; k < dim; k++)
        //            {
        //                Rx0[k] = center[k];
        //            }

        //            double RRedshift = NumericalRecipes.NR_Redshift.Estimate_Redshift(Rx, Ry, Rd, Rx0);
        //            double avgR = 0;
        //            for (int k = 0; k < count; k++)
        //            {
        //                avgR += Ry[k];
        //            }
        //            avgR /= count;
        //            //return record structure
        //            if (Microsoft.SqlServer.Server.SqlContext.IsAvailable)
        //            {
        //                Microsoft.SqlServer.Server.SqlDataRecord sqlrecord = (Microsoft.SqlServer.Server.SqlDataRecord)record;
        //                sqlrecord.SetDouble(0, center[dim]);
        //                sqlrecord.SetDouble(1, RRedshift);
        //                sqlrecord.SetDouble(2, avgR);
        //                sqlrecord.SetDouble(3, nearestpoints[0][dim + 1]);
        //                sqlrecord.SetDouble(4, nearestpoints[1][dim + 1]);
        //                sqlrecord.SetInt32(5, count);
        //                sqlrecord.SetInt32(6, cellread);
        //                SqlContext.Pipe.SendResultsRow(sqlrecord);
        //            }
        //        }
        //        cn.Close();
        //    }
        //    SqlContext.Pipe.SendResultsEnd();
        //    Alltime.Stop();
        //    Core.Log(Alltime.ElapsedMilliseconds.ToString() + " total");
        //}






        ///// <summary>
        ///// Not used, and will be removed
        ///// </summary>
        ///// <param name="EstimateForPoints"></param>
        ///// <param name="ReferencePoints"></param>
        ///// <param name="Dimension"></param>
        //[Microsoft.SqlServer.Server.SqlProcedure]
        //public static void EstimateValueForBox(SqlString EstimateForPoints, SqlString ReferencePoints, SqlInt32 Dimension)
        //{
        //    //Total time
        //    System.Diagnostics.Stopwatch totaltime = new System.Diagnostics.Stopwatch();
        //    System.Diagnostics.Stopwatch testimateZ = new System.Diagnostics.Stopwatch();
        //    double[] Querypoint;
        //    List<double[]> EstimateFor = new List<double[]>();
        //    List<double[]> Reference = new List<double[]>();
        //    bool readsucc;
        //    int Dim = (int)Dimension;
        //    totaltime.Start();
        //    using (SqlConnection cn = new SqlConnection(Constants.MyConnectionString))
        //    {
        //        cn.Open();
        //        using (SqlCommand cmd = new SqlCommand(EstimateForPoints.ToString(), cn))
        //        {
        //            using (SqlDataReader r = cmd.ExecuteReader())
        //            {
        //                readsucc = r.Read();
        //                while (readsucc)
        //                {
        //                    Querypoint = new double[Dim];
        //                    for (int i = 0; i < Dim; i++)
        //                    {
        //                        Querypoint[i] = (double)r[i];
        //                    }
        //                    EstimateFor.Add(Querypoint);
        //                    readsucc = r.Read();
        //                }
        //            }
        //        }
        //        using (SqlCommand cmd = new SqlCommand(ReferencePoints.ToString(), cn))
        //        {
        //            using (SqlDataReader r = cmd.ExecuteReader())
        //            {
        //                readsucc = r.Read();
        //                while (readsucc)
        //                {
        //                    Querypoint = new double[Dim + 1];
        //                    for (int i = 0; i < Dim; i++)
        //                    {
        //                        Querypoint[i] = (double)r[i];
        //                    }
        //                    Querypoint[Dim] = (double)r[Dim];
        //                    Reference.Add(Querypoint);
        //                    readsucc = r.Read();
        //                }
        //            }
        //        }
        //        cn.Close();
        //    }
        //    int count = Reference.Count;
        //    double[][] Rx = new double[count][];
        //    double[] Ry = new double[count];
        //    double[] Rd = new double[count];
        //    testimateZ.Start();
        //    for (int i = 0; i < count; i++)
        //    {
        //        Rx[i] = new double[Dim];
        //        for (int k = 0; k < Dim; k++)
        //        {
        //            Rx[i][k] = Reference[i][k];

        //        }
        //        //value
        //        Ry[i] = Reference[i][Dim];
        //    }
        //    for (int pnum = 0; pnum < EstimateFor.Count; pnum++)
        //    {
        //        for (int i = 0; i < count; i++)
        //        {
        //            Rd[i] = GetDistance(EstimateFor[pnum], Reference[i], Dim);
        //        }

        //        double[] Rx0 = new double[Dim];
        //        for (int k = 0; k < Dim; k++)
        //        {
        //            Rx0[k] = EstimateFor[pnum][k];
        //        }
        //        double RRedshift = NumericalRecipes.NR_Redshift.Estimate_Redshift(Rx, Ry, Rd, Rx0);
        //        Core.Log(RRedshift.ToString() + "  of " + Reference[0][Dim].ToString());
        //    }

        //    totaltime.Stop();
        //    testimateZ.Stop();
        //    Core.Log(testimateZ.ElapsedMilliseconds.ToString() + " ms estimate redshift");
        //    Core.Log(totaltime.ElapsedMilliseconds.ToString() + " ms total");


        //}




        ///// <summary>
        ///// Search for the N nearest neighbors of a given point
        ///// returns only ids
        ///// </summary>
        ///// <param name="name">the name of the kd-tree index</param>
        ///// <param name="coord">the coordinates of the point</param>
        ///// <param name="count">N</param>
        ///// <param name="originalid">Specify a point id to exclude from the list<note>this parameter is used to eliminate the point itself from its neighboor</note></param>
        //[Microsoft.SqlServer.Server.SqlProcedure]


        //public static void NearestNeighborsIDsBalancedTree(SqlString name, SqlBinary coord, int count, int originalid)
        //{

        //    //Total time
        //    System.Diagnostics.Stopwatch totaltime = new System.Diagnostics.Stopwatch();


        //    totaltime.Start();


        //    //Get the index by name
        //    BalancedTreeIndex index = GetIndex(name);


        //    int dim = index.Dimensions;

        //    //Number of cell read from SQL server
        //    int cellread = 0;




        //    //The point which neighbors we searching
        //    double[] center = Converters.VectorConverter.ToArray(coord.Value);
        //    //double[] center = new double[Converters.VectorConverter.ToArray(coord.Value).GetLength(0)];
        //    //Converters.VectorConverter.ToArray(coord.Value).CopyTo(center,0);


        //    //the Kd tree cells (ids) which have been queried already from the sql server
        //    System.Collections.ArrayList Leaves = new System.Collections.ArrayList();

        //    //The actual nearest vectors,
        //    double[] nearestpoints = new double[count];


        //    //The actual nearest vectors,
        //    int[] nearestIds = new int[count];

        //    for (int k = 0; k < count; k++)
        //    {
        //        nearestpoints[k] = double.MaxValue;

        //    }
        //    //The actual farest point distance
        //    double farestpointdistance = double.MaxValue;

        //    //The index of this point
        //    int farestpointindex = 0;


        //    //The actual nearest bounding box point
        //    double nearestboundingdistance = 0;

        //    //The index of this point
        //    int nearestboundingindex = 0;

        //    //The actual Bounding points
        //    //empty at start
        //    //key is the distance from the start point
        //    List<double[]> Bounds = new List<double[]>();

        //    int numberofRows = 0;

        //    double temp = 0;
        //    //The points which completed, no need to register them again..
        //    List<double[]> BoundPointsCompleted = new List<double[]>();
        //    BoundPointsCompleted.Add(center);

        //    //First determine in which cells the point belongs to
        //    int ActualCell = index.LookupIndex(center);


        //    string columns = "";

        //    //add the distance (square) to the columns
        //    //(R-r*R-r)+(G*G)+(E*E)

        //    string distancecolumn = "";
        //    for (int i = 0; i < dim; i++)
        //    {
        //        if (i != 0) distancecolumn += "+";
        //        distancecolumn += ("((" + index.dataColumnNamesArray[i] + "-" + center[i].ToString() + ")*(" + index.dataColumnNamesArray[i] + "-" + center[i].ToString() + "))");
        //    }
        //    columns += distancecolumn;
        //    columns += " ,id";
        //    double queriedpoint;
        //    int queriedpointID = 0;

        //    Boolean readsucc = false;
        //    using (SqlConnection cn = new SqlConnection(Constants.MyConnectionString))
        //    {
        //        cn.Open();
        //        farestpointdistance = nearestpoints[count - 1];


        //        while (farestpointdistance > nearestboundingdistance)
        //        {


        //            farestpointindex = 0;

        //            cellread++;
        //            //Add new Cell
        //            //Read points from DB
        //            string sql = Elte.SqlSpIdx.Indices.BalancedTree.BalancedTreeScripts.KD_QueryBox;

        //            sql = sql.Replace("$columns", " top " + count.ToString() + " " + columns);
        //            sql = sql.Replace("$Table", index.Table);
        //            sql = sql.Replace("$Indexname", index.IndexColumn);
        //            sql = sql.Replace("$cellnumber", ActualCell.ToString());
        //            if (farestpointdistance != double.MaxValue)
        //                sql += " and " + distancecolumn + " < " + farestpointdistance.ToString();
        //            sql += " order by " + distancecolumn;

        //            //    Core.Log(ActualCell.ToString() + " actualcell");
        //            //  Core.Log(sql);
        //            using (SqlCommand cmd = new SqlCommand(sql, cn))
        //            {



        //                using (SqlDataReader r = cmd.ExecuteReader())
        //                {

        //                    readsucc = r.Read();

        //                    while (readsucc)
        //                    {
        //                        numberofRows++;
        //                        //tgetdistance.Start();
        //                        // t3.Start();
        //                        //for (int i = 0; i < dim; i++)
        //                        //   {
        //                        //       queriedpoint[i] = (double)r[i];
        //                        //   }
        //                        //GetDistance(center, queriedpoint, dim);
        //                        //t3.Stop();
        //                        //tgetdistance.Stop();

        //                        temp = (double)r[0];


        //                        if (temp < farestpointdistance)
        //                        {
        //                            queriedpointID = (int)r[1];
        //                            queriedpoint = temp;

        //                            while (farestpointindex < count && nearestpoints[farestpointindex] <= temp)
        //                            {
        //                                farestpointindex++;
        //                            }
        //                            if (farestpointindex >= count)
        //                            {
        //                                farestpointdistance = nearestpoints[count - 1];
        //                                break;
        //                            }
        //                            if (queriedpointID != originalid)
        //                            {
        //                                for (int i = count - 1; i > farestpointindex; i--)
        //                                {
        //                                    if (nearestpoints[i - 1] != double.MaxValue)
        //                                    {
        //                                        nearestpoints[i] = nearestpoints[i - 1];
        //                                        nearestIds[i] = nearestIds[i - 1];
        //                                    }
        //                                }
        //                                nearestpoints[farestpointindex] = queriedpoint;
        //                                nearestIds[farestpointindex] = queriedpointID;
        //                                farestpointindex++;
        //                                farestpointdistance = nearestpoints[count - 1];
        //                                if (farestpointindex >= count)
        //                                {
        //                                    break;
        //                                }
        //                            }
        //                        }


        //                        readsucc = r.Read();

        //                    }

        //                }

        //            }



        //            Leaves.Add(ActualCell);
        //            //add the bounding points of it
        //            bool contains = false;
        //            foreach (double[] actualpoint in index.GetBoxPoints(ActualCell, center))
        //            {
        //                //Previous point?
        //                foreach (double[] boundpoint in BoundPointsCompleted)
        //                {
        //                    contains = true;
        //                    for (int i = 0; i < dim; i += 1)
        //                    {
        //                        if (boundpoint[i] != actualpoint[i])
        //                        {
        //                            contains = false;
        //                            break;
        //                        }
        //                    }
        //                    if (contains) break;
        //                }
        //                //Already in actual list?
        //                if (!contains)
        //                {
        //                    foreach (double[] boundpoint in Bounds)
        //                    {
        //                        contains = true;
        //                        for (int i = 0; i < dim; i += 1)
        //                        {
        //                            if (boundpoint[i] != actualpoint[i])
        //                            {
        //                                contains = false;
        //                                break;
        //                            }
        //                        }
        //                        if (contains) break;
        //                    }
        //                }
        //                if (!contains)
        //                {
        //                    Bounds.Add(actualpoint);
        //                }
        //            }
        //            //int[]  Clls = index.GetLeavesInvolved(dim, Bounds,center);
        //            Boolean foundnewBox = false;
        //            while (!foundnewBox)
        //            {
        //                //Search for the nearest bounding box point
        //                //double nearestboundingdistance = 0;

        //                //The index of this point
        //                //int nearestboundingindex = 0;
        //                nearestboundingdistance = double.MaxValue;
        //                nearestboundingindex = 0;
        //                for (int i = 0; i < Bounds.Count; i++)
        //                {
        //                    if (Bounds[i][dim] < nearestboundingdistance)
        //                    {
        //                        nearestboundingdistance = Bounds[i][dim];
        //                        nearestboundingindex = i;
        //                    }

        //                }
        //                double[] searchpoint = new double[dim];
        //                for (int i = 0; i < dim; i++)
        //                {
        //                    searchpoint[i] = Bounds[nearestboundingindex][i];
        //                    if (Bounds[nearestboundingindex][i] > center[i]) searchpoint[i] += 0.1F;
        //                    if (Bounds[nearestboundingindex][i] < center[i]) searchpoint[i] -= 0.1F;

        //                }

        //                ActualCell = index.LookupIndex(searchpoint);
        //                BoundPointsCompleted.Add(Bounds[nearestboundingindex]);
        //                Bounds.RemoveAt(nearestboundingindex);
        //                //if already contains it continue with the next point
        //                if (!Leaves.Contains(ActualCell)) foundnewBox = true;
        //            }

        //        }
        //        cn.Close();
        //    }
        //    totaltime.Stop();
        //    Core.Log(totaltime.ElapsedMilliseconds.ToString() + " ms total , " + cellread.ToString() + " Cells read " + numberofRows.ToString() + " rows returned");

        //    if (Microsoft.SqlServer.Server.SqlContext.IsAvailable)
        //    {
        //        //return record structure
        //        SqlDataRecord record = new SqlDataRecord(
        //                    new SqlMetaData("Original", SqlDbType.Int),
        //                    new SqlMetaData("Neighbour", SqlDbType.Int)
        //        );


        //        //Send the results
        //        SqlContext.Pipe.SendResultsStart(record);
        //        for (int i = 0; i < count; i++)
        //        {
        //            record.SetSqlInt32(0, originalid);
        //            record.SetSqlInt32(1, nearestIds[i]);
        //            SqlContext.Pipe.SendResultsRow(record);
        //        }
        //        SqlContext.Pipe.SendResultsEnd();
        //    }



        //}

        // non used function for debug purposes
        //public int[] GetLeavesInvolved(int dim, List<double[]> Bounds, double[] center)
        //{
        //    int temp;
        //    List<int> Cells=new List<int>();
        //    for (int k = 0; k < Bounds.Count; k++)
        //    {
        //        double[] searchpoint = new double[dim];
        //        for (int i = 0; i < dim; i++)
        //        {
        //            searchpoint[i] = Bounds[k][i];
        //            if (Bounds[k][i] > center[i]) searchpoint[i] += 0.001F;
        //            if (Bounds[k][i] < center[i]) searchpoint[i] -= 0.001F;
        //        }
        //        temp = SearchBox(searchpoint);
        //        if (!Cells.Contains(temp)) Cells.Add(SearchBox(searchpoint));
        //    }
        //    return Cells.ToArray();
        //}
        #endregion
        public System.Collections.Generic.List<long> ResultBoxes;
        public HalfspaceList SearchHalfSpaceList;
        private void IterateBoxes(int Boxid)
        {

            Core.Log(Boxid.ToString());
            Halfspace.InsideType result = SearchHalfSpaceList.IsInsideBox(BoundingBoxesReal[Boxid]);
            if (result == Halfspace.InsideType.None) return;
            if (result == Halfspace.InsideType.All || Boxid > Math.Pow(2, this.TreeDepth))
            {
                ResultBoxes.Add(Boxid);
                return;
            }
            if (result == Halfspace.InsideType.Part)
            {
                IterateBoxes(Boxid * 2);
                IterateBoxes(Boxid * 2 + 1);
            }

        }
        //public override IEnumerable<long> SpatialQueryCore(string query)
        //{
        //    this.LoadBoxes();
        //    ResultBoxes = new List<long>();
        //    Parser.Parser p = new Parser.Parser(new Parser.Tokenizer(), new Parser.VariableMapper(this.DataColumnNames));
        //    SearchHalfSpaceList = p.Parse(query, Dimensions);
        //    IterateBoxes(1);
        //    return ResultBoxes;
        //}



        public override IEnumerable SpherePoints(double[] coord, double R)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        int laststagemin;//(int)Math.Pow(2, TreeDepth);
        int laststagemax; //(int)Math.Pow(2, TreeDepth + 1) - 1;
        private string involvedCellsWhere(IEnumerator<int> actualcell)
        {
            laststagemin = (int)Math.Pow(2, TreeDepth);
            laststagemax = (int)Math.Pow(2, TreeDepth + 1) - 1;
            string whereInPart = "";
            string whereBetweenPart = "";
            while (actualcell.MoveNext())
            {
                int leveltoadd = 0;

                int currentcell = actualcell.Current;

                if (!(laststagemin <= currentcell && currentcell <= laststagemax))
                {

                    int endcell = currentcell;
                    while (!(laststagemin <= currentcell && currentcell <= laststagemax))
                    {
                        leveltoadd++;
                        currentcell *= 2;
                        endcell = endcell * 2 + 1;
                    }
                    whereBetweenPart += (whereBetweenPart != "") ? " or " : "";
                    whereBetweenPart += IndexColumn + " between " + currentcell.ToString() + " and " + endcell.ToString();
                }
                else
                {
                    whereInPart += (whereInPart != "") ? "," : "";
                    whereInPart += currentcell.ToString();

                }
            }
            string result = "";
            if (whereInPart != "")
            {
                result = IndexColumn + " in (" + whereInPart + ")";
                if (whereBetweenPart != "") result += " or ";
            }
            if (whereBetweenPart != "") result += whereBetweenPart;
            return result;
        }
        public override IEnumerable<object[]> SpatialQuery(string selectlist, string spatialwhere, string sqlwhere)
        {
            if (this.DatabaseConnection.State != ConnectionState.Open)
            {
                this.DatabaseConnection.Open();
            }
            //which cells involved?
            IEnumerator<int> involvedcells = Cellids(spatialwhere).GetEnumerator();

            //convert spatial where condition to sql too
            if (sqlwhere != "") sqlwhere = "(" + sqlwhere + ") and ";
            sqlwhere += Core.Spatial2sql(spatialwhere, this.Dimensions);


            object[] results = null;
            string cellstring = "";
            cellstring = involvedCellsWhere(involvedcells);
            if (cellstring != "") sqlwhere = "(" + cellstring + ") and " + sqlwhere;

            output += s.ElapsedMilliseconds.ToString() + " cells  " + Environment.NewLine;
            //while (involvedcells.MoveNext())
            //{
            // int actualcell = involvedcells.Current;
            string sql = Elte.SqlSpIdx.Indices.BalancedTree.BalancedTreeScripts.KD_QueryBox;
            sql = sql.Replace("$columns", selectlist); //((count == 0) ? "" : (" top " + count.ToString())) + " " + columns
            sql = sql.Replace("$Table", this.Table);
            sql = sql.Replace("$Indexname", this.IndexColumn);


            sql = sql.Replace("[$where]", sqlwhere);
            //Core.Log(sql);

            bool firsread = true;

            using (SqlCommand cmd = new SqlCommand(sql, this.databaseConnection))
            {
                output += s.ElapsedMilliseconds.ToString() + "  " + cellstring + " Before  " + Environment.NewLine;
                using (SqlDataReader r = cmd.ExecuteReader())
                {
                    while (r.Read())
                    {
                        if (firsread)
                        {
                            firsread = false;
                            results = new object[r.VisibleFieldCount];
                            output += s.ElapsedMilliseconds.ToString() + "  " + cellstring + " after  " + Environment.NewLine;
                        }
                        r.GetValues(results);
                        yield return results;
                    }
                }

            }
            //}


        }

    }


}
